SQLite Cheat Sheet

SELECT

Pick columns to return. Use * for all.

SELECT invoice_id, amount
FROM invoices;

WHERE, filter rows

Operators: = != < > BETWEEN IN LIKE. Combine with AND / OR.

SELECT *
FROM invoices
WHERE invoice_date BETWEEN '2026-01-01' AND '2026-03-31'
  AND amount > 1000;

ORDER BY

ASC (default) or DESC. Can sort by multiple columns.

SELECT customer_id, SUM(amount) AS total
FROM invoices
GROUP BY customer_id
ORDER BY total DESC;

LIMIT

Take only the top N rows. Use with ORDER BY for "top 10."

SELECT *
FROM invoices
ORDER BY amount DESC
LIMIT 10;

The five aggregates

GROUP BY

One row per group. Every non-aggregated column in SELECT must appear in GROUP BY.

SELECT segment, COUNT(*) AS n,
       SUM(amount) AS total
FROM invoices i
JOIN customers c ON c.customer_id = i.customer_id
GROUP BY segment;

HAVING, filter on aggregates

WHERE filters rows BEFORE grouping. HAVING filters AFTER.

SELECT customer_id, SUM(amount) AS total
FROM invoices
GROUP BY customer_id
HAVING SUM(amount) > 50000;

Round for readability

SQLite often returns long decimals. Wrap aggregates in ROUND(..., 2).

SELECT ROUND(AVG(amount), 2)
FROM invoices;

INNER JOIN, only rows in both

An invoice without a customer (impossible here, but if it happened) would be dropped.

SELECT i.*, c.customer_name
FROM invoices i
INNER JOIN customers c ON c.customer_id = i.customer_id;

LEFT JOIN, keep all left rows

Use LEFT when the left-side rows must all appear in the result. An invoice with zero payments still needs a row in your aging report, otherwise the unpaid invoices vanish. An INNER JOIN here would silently drop them and your AR total would be too low.

SELECT i.invoice_id, i.amount,
       COALESCE(SUM(p.amount), 0) AS paid
FROM invoices i
LEFT JOIN payments p ON p.invoice_id = i.invoice_id
GROUP BY i.invoice_id;

Rule of thumb: if you're counting or summing things from the left table, LEFT JOIN is almost always what you want.

Multi-table JOIN

Chain joins to walk relationships. invoices → customers → invoice_terms uses two joins.

SELECT i.invoice_id, c.customer_name, t.terms_name
FROM invoices i
JOIN customers c ON c.customer_id = i.customer_id
JOIN invoice_terms t ON t.terms_id = c.terms_id;

Remember: terms_id is on customers, not invoices.

RIGHT and FULL, exist but rare

SQLite supports RIGHT JOIN and FULL OUTER JOIN (since SQLite 3.39). You almost never need them, just flip the order of tables and use LEFT.

Days between two dates

SQLite stores dates as text. To subtract them, use julianday() on both sides.

SELECT julianday('2026-04-30') - julianday(due_date)
       AS days_past_due
FROM invoices;

Result is a real number. Wrap in CAST(... AS INTEGER) for whole days.

Add days to a date

Use the date() function with a modifier.

SELECT
  invoice_date,
  date(invoice_date, '+30 days') AS net_30_due
FROM invoices;

Modifiers: '+N days', '-N months', 'start of month', 'start of year'.

Date comparisons in WHERE

ISO date strings compare correctly as text. No conversion needed.

SELECT *
FROM payments
WHERE payment_date >= '2026-01-01'
  AND payment_date <= '2026-04-30';

Today's date

SELECT date('now');
-- returns '2026-05-23' (or whatever today is)

The shape, bucket a value

For aging buckets, status mapping, conditional flags.

SELECT invoice_id, due_date,
  CASE
    WHEN days_past_due <= 0  THEN 'Current'
    WHEN days_past_due <= 30 THEN '1-30'
    WHEN days_past_due <= 60 THEN '31-60'
    WHEN days_past_due <= 90 THEN '61-90'
    ELSE 'Over 90'
  END AS aging_bucket
FROM ...;

Custom sort order for buckets

Text sort puts "1-30" before "Current". Force the order with CASE in ORDER BY.

ORDER BY CASE aging_bucket
  WHEN 'Current' THEN 1
  WHEN '1-30'    THEN 2
  WHEN '31-60'   THEN 3
  WHEN '61-90'   THEN 4
  WHEN 'Over 90' THEN 5
END;

What a CTE is

A "common table expression" lets you name a sub-query so the main query stays readable. Think of it as defining a temporary table.

WITH applied AS (
  SELECT invoice_id, SUM(amount) AS paid_total
  FROM payments
  GROUP BY invoice_id
)
SELECT i.*, a.paid_total
FROM invoices i
LEFT JOIN applied a ON a.invoice_id = i.invoice_id;

Multiple CTEs

Comma-separate. Each CTE can reference earlier ones.

WITH applied AS (...),
     balances AS (
       SELECT i.invoice_id, i.amount - COALESCE(a.paid_total, 0)
              AS balance
       FROM invoices i
       LEFT JOIN applied a ON ...
     )
SELECT * FROM balances WHERE balance > 0;

CREATE VIEW, re-computes on every read

The view is a saved query, not a saved result. Reading the view re-runs the query against current data.

DROP VIEW IF EXISTS vw_aging_current;

CREATE VIEW vw_aging_current AS
WITH applied AS (...)
SELECT ... FROM ...;

-- tomorrow, this re-computes:
SELECT * FROM vw_aging_current;

CREATE TABLE AS SELECT, frozen snapshot

The table holds the rows the SELECT produced at creation time. Future writes to the underlying tables don't change it.

DROP TABLE IF EXISTS ar_aging_2025_12_31;

CREATE TABLE ar_aging_2025_12_31 AS
SELECT ... -- with '2025-12-31' as the as-of date;

Use this for month-end and year-end snapshots that must not move when payments arrive later.

COALESCE, substitute for NULL

After a LEFT JOIN, the right-side columns are NULL when there's no match. Use COALESCE to substitute a sensible default.

SELECT i.invoice_id,
       i.amount - COALESCE(p.paid_total, 0) AS balance
FROM invoices i
LEFT JOIN ... ;

IS NULL, find missing rows

Cannot use = NULL. Always IS NULL / IS NOT NULL.

-- Find invoices with no payments at all
SELECT i.invoice_id
FROM invoices i
LEFT JOIN payments p ON p.invoice_id = i.invoice_id
WHERE p.payment_id IS NULL;

Format dates in ISO

Always use YYYY-MM-DD. SQLite stores dates as text, ISO format is the only one that sorts and compares correctly.

Use single quotes for strings

'Net 30' is a string. "Net 30" in SQLite is a quoted identifier, it'll try to find a column named "Net 30" and fail.

Comment your queries

-- single line or /* multi line */. Use them generously in your notebook so future-you can read it.

Test with LIMIT

When you're not sure a query is right, add LIMIT 5 to inspect before unleashing on the full 20K rows.

Aliases everywhere

FROM invoices i lets you write i.invoice_id throughout. Saves typing and clarifies which table a column comes from.