Backend Live Coding

-- ======================================================================
-- Q: How do you read an EXPLAIN ANALYZE? What do you look for first?
--
-- I read from the inside out. I check:
-- - estimated rows vs actual rows: a big difference means stale statistics
--   (ANALYZE) or correlated columns (CREATE STATISTICS).
-- - Seq Scan on a large table with a selective filter: an index is missing or
--   cannot be used (function on the column, cast, LIKE '%x').
-- - Large Rows Removed by Filter: the index covers only part of the WHERE, maybe
--   a composite or partial index is needed.
-- - loops in a Nested Loop: actual time × loops is the real time.
-- - Sort Method: external merge Disk: work_mem is not enough or an index is
--   needed for the ORDER BY.
-- - Buffers shared read vs hit: how many pages came from disk.
-- In practice I start from pg_stat_statements (which queries cost the most in
-- total) and then run EXPLAIN on the specific one.
-- ======================================================================

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Limit  (cost=0.43..58.12 rows=20 width=96) (actual time=0.041..0.210 rows=20 loops=1)
  ->  Index Scan using ix_orders_cust_created on orders
        (cost=0.43..1210.55 rows=419 width=96) (actual time=0.040..0.205 rows=20 loops=1)
        Index Cond: (customer_id = 42)
        Filter: (status = 'paid')
        Rows Removed by Filter: 3
        Buffers: shared hit=24
Planning Time: 0.180 ms
Execution Time: 0.240 ms

-- ======================================================================
-- Q: Find the B2B customers with more than 10 orders in the last quarter and an
-- average value above 500 EUR.
--
-- WHERE cuts rows before grouping (cheaper, can use an index), HAVING filters on
-- the aggregates. FILTER (WHERE ...) is cleaner than SUM(CASE WHEN ...). Amounts
-- always in cents or numeric, never float.
-- ======================================================================

SELECT o.customer_id,
       COUNT(*)                         AS orders_cnt,
       AVG(o.total_cents) / 100.0       AS avg_eur,
       COUNT(*) FILTER (WHERE o.channel = 'b2b_portal') AS portal_orders
FROM orders o
JOIN customers c ON c.id = o.customer_id AND c.kind = 'b2b'
WHERE o.created_at >= now() - interval '3 months'
  AND o.status IN ('paid', 'shipped')
GROUP BY o.customer_id
HAVING COUNT(*) > 10 AND AVG(o.total_cents) > 50000
ORDER BY avg_eur DESC;

-- ======================================================================
-- Q: Find customers who have never placed an order. Why not NOT IN?
--
-- x NOT IN (1, 2, NULL) evaluates to x <> 1 AND x <> 2 AND x <> NULL, and the
-- last one is UNKNOWN, so no row passes. NOT EXISTS does not have this problem
-- and is usually faster too. I ran into this in the ASP migration: the legacy
-- data had orders with an empty customer, and a report using NOT IN suddenly
-- returned an empty result.
-- ======================================================================

-- Correct: anti-join, the planner turns it into a Hash Anti Join
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dangerous: if even one orders.customer_id is NULL, it returns 0 rows
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- ======================================================================
-- Q: Compute daily revenue, the running total for the month and the change from
-- the previous day.
--
-- The CTE does the per-day aggregate, and then the window functions work on the
-- small result. I declare ROWS BETWEEN explicitly because the default frame is
-- RANGE, which gives "odd" sums when there are duplicate values in the ORDER BY.
-- NULLIF(x, 0) avoids division by zero. Days without sales are missing, so if a
-- continuous series is needed I LEFT JOIN to generate_series.
-- ======================================================================

WITH daily AS (
    SELECT date_trunc('day', created_at)::date AS day,
           SUM(total_cents)                    AS revenue
    FROM orders
    WHERE status = 'paid'
      AND created_at >= date_trunc('month', now())
    GROUP BY 1
)
SELECT day,
       revenue,
       SUM(revenue) OVER (ORDER BY day
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY day)                            AS diff_prev_day,
       ROUND(100.0 * (revenue - LAG(revenue) OVER w)
             / NULLIF(LAG(revenue) OVER w, 0), 1)                            AS pct_change
FROM daily
WINDOW w AS (ORDER BY day)
ORDER BY day;

-- ======================================================================
-- Q: Write a query that returns the 3 most recent orders of each customer.
--
-- The classic top-N per group is ROW_NUMBER with PARTITION BY. I add a second
-- key to the ORDER BY (id) so the result is deterministic on ties. If there are
-- few customers and many orders, LATERAL with an index does an index scan with
-- LIMIT per customer instead of sorting the whole table. In Django the
-- equivalent is Window(RowNumber(), partition_by=...), and since Django 4.2 you
-- can filter directly on a window annotation.
-- ======================================================================

SELECT customer_id, id, created_at, total_cents
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY created_at DESC, id DESC) AS rn
    FROM orders o
    WHERE o.status <> 'cancelled'
) t
WHERE rn <= 3
ORDER BY customer_id, rn;

-- Alternative with LATERAL: very fast when an index (customer_id, created_at DESC) exists
SELECT c.id, o.id, o.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, created_at FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC LIMIT 3
) o;