SQL cheat sheet for CSV & JSON analysis

Every query below is standard SQLite syntax — the exact SQL Synth runs against your uploaded CSV or JSON file. Copy any of these into the query editor, swap in your own table and column names, and run it.

Look at your data

SELECT * FROM orders LIMIT 10;

SELECT COUNT(*) FROM orders;

Filter rows

SELECT * FROM orders
WHERE status = 'shipped'
  AND total > 100;

SELECT * FROM customers
WHERE email IS NULL OR email = '';

Sort and limit

SELECT * FROM orders
ORDER BY total DESC
LIMIT 20;

Aggregate with GROUP BY

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

SELECT status, AVG(total) AS avg_order_value
FROM orders
GROUP BY status;

Join two tables

Once you've uploaded two files as separate tables (see how to join two CSV files), join them like any two relational tables:

SELECT customers.name, orders.total, orders.status
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Window functions

SELECT
  customer_id,
  total,
  RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_by_value
FROM orders;

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

Cast text to a number

Every uploaded column is stored as TEXT, so numeric operations need an explicit cast:

SELECT customer_id, CAST(total AS REAL) AS total_numeric
FROM orders
ORDER BY total_numeric DESC;

If a column has thousands-separator commas (like "1,200"), strip them before casting:

SELECT CAST(REPLACE(total, ',', '') AS REAL) AS total_numeric
FROM orders;

Dates and strings

SELECT * FROM orders
WHERE order_date >= date('now', '-30 days');

SELECT * FROM customers
WHERE name LIKE '%acme%';

Pull a value out of a JSON column

If you uploaded a JSON file with an array-valued field, use SQLite's own JSON functions directly:

SELECT id, json_extract(tags, '$[0]') AS first_tag
FROM orders
WHERE tags != '';

Frequently asked questions