Kaushik's Notes

E-commerce Practice Queries

17 min read

Overview

  • Purpose: One realistic schema + seed data used by every note in PostgreSQL Queries, plus 20 graded practice queries (easy → hard) with solutions
  • Domain: Small e-commerce shop — users, categories, products, orders, order_items, payments, plus an employees table for org-chart / salary questions
  • How to Use: Load the schema and seed once, try each question yourself, then expand the solution
  • Verified: Every block in this note was run against PostgreSQL 17

Spin Up a Scratch Database

docker run --rm -d --name pg-practice -e POSTGRES_PASSWORD=x -p 55432:5432 postgres:17
docker exec -it pg-practice psql -U postgres
-- paste the schema + seed blocks below

Entity Relationships

users 1──* orders 1──* order_items *──1 products *──1 categories (self-ref tree)
  │            └──* payments
  └── referred_by → users (self-reference)
 
employees.manager_id → employees (self-reference, org chart)

Sample Schema

DROP TABLE IF EXISTS payments, order_items, orders, products, categories, users, employees CASCADE;
 
CREATE TABLE users (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text NOT NULL,
    country     char(2),                                   -- NULL = unknown
    referred_by bigint REFERENCES users(id) ON DELETE SET NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE categories (
    id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL UNIQUE,
    parent_id int REFERENCES categories(id) ON DELETE RESTRICT
);
 
CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         text NOT NULL UNIQUE,
    name        text NOT NULL,
    category_id int NOT NULL REFERENCES categories(id),
    price       numeric(10,2) NOT NULL CHECK (price >= 0),
    stock       int NOT NULL DEFAULT 0 CHECK (stock >= 0),
    attributes  jsonb NOT NULL DEFAULT '{}',
    tags        text[] NOT NULL DEFAULT '{}',
    is_active   boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    bigint NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status     text NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(id),
    quantity   int NOT NULL CHECK (quantity > 0),
    unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),   -- price at time of purchase
    PRIMARY KEY (order_id, product_id)
);
 
CREATE TABLE payments (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    amount   numeric(10,2) NOT NULL CHECK (amount > 0),
    method   text NOT NULL CHECK (method IN ('card', 'upi', 'wallet')),
    status   text NOT NULL CHECK (status IN ('succeeded', 'failed', 'refunded')),
    paid_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE employees (
    id         int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    manager_id int REFERENCES employees(id),
    department text NOT NULL,
    salary     numeric(10,2) NOT NULL,
    hired_on   date NOT NULL
);
 
-- Postgres does NOT auto-index foreign key columns — add the ones you join/filter on
CREATE INDEX ON orders (user_id, created_at);
CREATE INDEX ON order_items (product_id);
CREATE INDEX ON payments (order_id);
CREATE INDEX ON products (category_id);

Seed Data

  • Deterministic IDs: Rows are inserted in order into fresh tables, so identity IDs are 1, 2, 3, …
  • Time Zone: Timestamps are written in UTC; run SET timezone = 'UTC'; so month/day results match
SET timezone = 'UTC';
 
INSERT INTO categories (name, parent_id) VALUES
  ('Electronics', NULL),   -- 1
  ('Computers',   1),      -- 2
  ('Laptops',     2),      -- 3
  ('Accessories', 2),      -- 4
  ('Phones',      1),      -- 5
  ('Home',        NULL),   -- 6
  ('Kitchen',     6),      -- 7
  ('Books',       NULL);   -- 8
 
INSERT INTO users (email, full_name, country, referred_by, created_at) VALUES
  ('asha@example.com',  'Asha Rao',    'IN', NULL, '2025-01-05 12:00+00'),  -- 1
  ('ben@example.com',   'Ben Carter',  'US', 1,    '2025-01-20 12:00+00'),  -- 2
  ('chen@example.com',  'Chen Wei',    'SG', 1,    '2025-02-11 12:00+00'),  -- 3
  ('diya@example.com',  'Diya Shah',   'IN', 2,    '2025-03-02 12:00+00'),  -- 4
  ('eva@example.com',   'Eva Muller',  'DE', NULL, '2025-03-15 12:00+00'),  -- 5
  ('farid@example.com', 'Farid Khan',  'IN', 4,    '2025-04-01 12:00+00'),  -- 6
  ('gina@example.com',  'Gina Lopez',  'US', NULL, '2025-05-09 12:00+00'),  -- 7 (never orders)
  ('hiro@example.com',  'Hiro Tanaka', NULL, NULL, '2025-06-21 12:00+00');  -- 8 (never orders, no country)
 
INSERT INTO products (sku, name, category_id, price, stock, attributes, tags, is_active) VALUES
  ('LAP-001', 'Air Laptop 13',  3,  999.00,  15, '{"brand":"Zen","ram_gb":16,"color":"silver"}',                    '{laptop,ultrabook}', true),
  ('LAP-002', 'Pro Laptop 16',  3, 2199.00,   5, '{"brand":"Zen","ram_gb":32,"color":"grey","specs":{"cpu":"M4"}}', '{laptop,pro}',       true),
  ('ACC-001', 'Wireless Mouse', 4,   25.00, 200, '{"brand":"Clicky","wireless":true}',                              '{mouse,wireless}',   true),
  ('ACC-002', 'Mechanical Keyboard', 4, 89.00, 60, '{"brand":"Clicky","wireless":false,"switch":"brown"}',          '{keyboard}',         true),
  ('ACC-003', 'USB-C Hub',      4,   45.00,   0, '{"brand":"Portly"}',                                              '{usb,hub}',          true),
  ('PHN-001', 'Pixel Phone',    5,  699.00,  30, '{"brand":"Gogo","color":"black","storage_gb":128}',               '{phone,android}',    true),
  ('PHN-002', 'Fruit Phone',    5,  999.00,  25, '{"brand":"Fruit","color":"white","storage_gb":256}',              '{phone,ios}',        true),
  ('KIT-001', 'Espresso Maker', 7,  349.00,  10, '{"brand":"Brew","color":"red"}',                                  '{coffee,kitchen}',   true),
  ('KIT-002', 'Chef Knife',     7,   59.00,  40, '{"brand":"Sharp"}',                                               '{knife,kitchen}',    true),
  ('BOK-001', 'SQL Performance Explained', 8, 35.00, 100, '{"author":"Winand","pages":200}',                        '{book,sql}',         true),
  ('BOK-002', 'Designing Data-Intensive Apps', 8, 45.00, 80, '{"author":"Kleppmann","pages":600}',                  '{book,systems}',     true),
  ('ACC-004', 'Laptop Stand',   4,   39.00,  50, '{"brand":"Portly","color":"silver"}',                             '{stand}',            false);
 
INSERT INTO orders (user_id, status, created_at) VALUES
  (1, 'delivered', '2025-01-10 12:00+00'),  -- 1
  (1, 'delivered', '2025-02-14 12:00+00'),  -- 2
  (2, 'delivered', '2025-02-20 12:00+00'),  -- 3
  (3, 'shipped',   '2025-03-05 12:00+00'),  -- 4
  (2, 'cancelled', '2025-03-18 12:00+00'),  -- 5
  (4, 'delivered', '2025-03-25 12:00+00'),  -- 6
  (1, 'paid',      '2025-04-02 12:00+00'),  -- 7
  (5, 'delivered', '2025-04-15 12:00+00'),  -- 8
  (3, 'delivered', '2025-05-01 12:00+00'),  -- 9
  (6, 'pending',   '2025-05-20 12:00+00'),  -- 10
  (4, 'shipped',   '2025-06-03 12:00+00'),  -- 11
  (5, 'paid',      '2025-06-10 12:00+00'),  -- 12
  (2, 'delivered', '2025-06-28 12:00+00'),  -- 13
  (6, 'delivered', '2025-07-04 12:00+00'),  -- 14
  (1, 'pending',   '2025-07-15 12:00+00');  -- 15
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 999.00), (1, 3, 1, 25.00),
  (2, 10, 2, 35.00), (2, 11, 1, 45.00),
  (3, 6, 1, 699.00), (3, 3, 2, 25.00),
  (4, 2, 1, 2199.00),
  (5, 7, 1, 999.00),
  (6, 8, 1, 349.00), (6, 9, 2, 59.00),
  (7, 4, 1, 89.00),  (7, 3, 1, 22.50),           -- discounted mouse
  (8, 1, 1, 999.00), (8, 4, 1, 89.00), (8, 5, 1, 45.00),
  (9, 10, 1, 35.00),
  (10, 6, 1, 699.00),
  (11, 11, 2, 45.00), (11, 10, 1, 35.00),
  (12, 7, 1, 999.00), (12, 3, 1, 25.00),
  (13, 8, 1, 349.00),
  (14, 9, 1, 59.00), (14, 10, 1, 35.00),
  (15, 2, 1, 2199.00);
 
INSERT INTO payments (order_id, amount, method, status, paid_at) VALUES
  (1,  1024.00, 'card',   'succeeded', '2025-01-10 12:05+00'),
  (2,   115.00, 'upi',    'succeeded', '2025-02-14 12:05+00'),
  (3,   749.00, 'card',   'failed',    '2025-02-20 12:01+00'),
  (3,   749.00, 'card',   'succeeded', '2025-02-20 12:04+00'),
  (4,  2199.00, 'card',   'succeeded', '2025-03-05 12:05+00'),
  (5,   999.00, 'wallet', 'refunded',  '2025-03-18 12:05+00'),
  (6,   467.00, 'upi',    'succeeded', '2025-03-25 12:05+00'),
  (7,   111.50, 'card',   'succeeded', '2025-04-02 12:05+00'),
  (8,  1000.00, 'card',   'succeeded', '2025-04-15 12:05+00'),
  (8,   133.00, 'wallet', 'succeeded', '2025-04-15 12:06+00'),   -- split payment
  (9,    35.00, 'upi',    'succeeded', '2025-05-01 12:05+00'),
  (11,  125.00, 'card',   'succeeded', '2025-06-03 12:05+00'),
  (12, 1024.00, 'wallet', 'succeeded', '2025-06-10 12:05+00'),
  (13,  349.00, 'card',   'succeeded', '2025-06-28 12:05+00'),
  (14,   94.00, 'upi',    'succeeded', '2025-07-04 12:05+00');
 
INSERT INTO employees (name, manager_id, department, salary, hired_on) VALUES
  ('Maya',  NULL, 'Exec',        300000, '2019-01-01'),  -- 1 CEO
  ('Raj',   1,    'Engineering', 200000, '2019-06-01'),  -- 2
  ('Lena',  1,    'Sales',       180000, '2020-02-01'),  -- 3
  ('Omar',  2,    'Engineering', 150000, '2020-08-15'),  -- 4
  ('Priya', 2,    'Engineering', 150000, '2021-03-01'),  -- 5
  ('Tom',   4,    'Engineering', 120000, '2022-05-10'),  -- 6
  ('Zoe',   4,    'Engineering', 110000, '2023-01-09'),  -- 7
  ('Sam',   3,    'Sales',        90000, '2021-11-20'),  -- 8
  ('Ivy',   3,    'Sales',        95000, '2022-07-01'),  -- 9
  ('Leo',   8,    'Sales',        70000, '2024-02-12');  -- 10
 
ANALYZE;

Data Facts Worth Knowing

  • Revenue Statuses: paid, shipped, delivered count as revenue; pending and cancelled do not
  • Edge Cases Built In: users with no orders (Gina, Hiro), a NULL country (Hiro), an inactive never-ordered product (Laptop Stand), an out-of-stock product (USB-C Hub), a split payment (order 8), a failed retry (order 3), a discounted line (order 7), a 3-level referral chain (Farid → Diya → Ben → Asha)

Practice Queries

Easy

Q1. Active products under 100, cheapest first

SELECT sku, name, price
FROM products
WHERE is_active AND price < 100
ORDER BY price, name;
  • Expected: 6 rows, Wireless Mouse (25.00) first, Mechanical Keyboard (89.00) last; Laptop Stand (39.00) is excluded because it is inactive

Q2. Number of users per country, showing unknown countries as ??

SELECT COALESCE(country, '??') AS country, count(*) AS users
FROM users
GROUP BY 1
ORDER BY users DESC, country;
  • Expected: IN 3, US 2, then ??, DE, SG with 1 each
  • Point: GROUP BY puts all NULLs in one group; COALESCE just relabels it

Q3. Number of orders in each status

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC, status;
  • Expected: delivered 8, paid 2, pending 2, shipped 2, cancelled 1

Q4. Total value of every order

SELECT o.id, o.status, sum(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id            -- o.status allowed: it depends functionally on the PK
ORDER BY o.id;
  • Expected: 15 rows; order 1 = 1024.00, order 8 = 1133.00, order 7 = 111.50

Q5. Stock value per category, only where it is above 5,000

SELECT c.name AS category, sum(p.price * p.stock) AS stock_value
FROM categories c
JOIN products p ON p.category_id = c.id
WHERE p.is_active
GROUP BY c.name
HAVING sum(p.price * p.stock) > 5000
ORDER BY stock_value DESC;
  • Expected: Phones 45945.00, Laptops 25980.00, Accessories 10340.00, Books 7100.00, Kitchen 5850.00
  • Point: WHERE filters rows before grouping; HAVING filters groups after

Medium

Q6. Users who have never placed an order

SELECT u.id, u.full_name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
ORDER BY u.id;
  • Expected: Gina Lopez, Hiro Tanaka
  • Point: Anti-join. Prefer NOT EXISTS over NOT IN (see Joins for the NULL trap)

Q7. Products that have never been ordered

SELECT p.sku, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
  • Expected: ACC-004 Laptop Stand
  • Point: LEFT JOIN … IS NULL is the other anti-join form; Postgres plans both as a Hash Anti Join

Q8. Monthly revenue and order count (revenue statuses only)

SELECT date_trunc('month', o.created_at)::date AS month,
       count(DISTINCT o.id)                    AS orders,
       sum(oi.quantity * oi.unit_price)        AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY 1
ORDER BY 1;
  • Expected: 7 months; Jan 1024.00, Feb 864.00, Mar 2666.00, Apr 1244.50, May 35.00, Jun 1498.00, Jul 94.00

Q9. Customer summary: orders, lifetime spend, last order (include users with no orders)

WITH order_totals AS (               -- aggregate items per order FIRST to avoid fan-out
    SELECT order_id, sum(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT u.full_name,
       count(o.id)                                   AS orders,
       COALESCE(sum(ot.total) FILTER (WHERE o.status IN ('paid','shipped','delivered')), 0) AS lifetime_spend,
       max(o.created_at)::date                       AS last_order
FROM users u
LEFT JOIN orders o        ON o.user_id = u.id
LEFT JOIN order_totals ot ON ot.order_id = o.id
GROUP BY u.id, u.full_name
ORDER BY lifetime_spend DESC, u.full_name;
  • Expected: Chen Wei 2234.00 first, then Eva Muller 2157.00; Asha has 4 orders but only 1250.50 counted (one is pending); Gina and Hiro show 0 orders and 0 spend
  • Point: count(o.id) (not count(*)) returns 0 for users with no orders; COALESCE turns the NULL sum into 0

Q10. Top 3 products by revenue, keeping ties

SELECT p.name, sum(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id AND o.status IN ('paid','shipped','delivered')
JOIN products p ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY revenue DESC
FETCH FIRST 3 ROWS WITH TIES;
  • Expected: Pro Laptop 16 2199.00, Air Laptop 13 1998.00, Fruit Phone 999.00
  • Point: WITH TIES adds any extra rows whose revenue equals the 3rd row’s value; LIMIT 3 would cut ties arbitrarily

Q11. Customers who bought both a laptop and a phone (non-cancelled orders)

SELECT u.full_name
FROM users u
JOIN orders o       ON o.user_id = u.id AND o.status <> 'cancelled'
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
JOIN categories c   ON c.id = p.category_id
WHERE c.name IN ('Laptops', 'Phones')
GROUP BY u.id, u.full_name
HAVING count(DISTINCT c.name) = 2;
  • Expected: Eva Muller
  • Point: “Has both X and Y” = filter to X or Y, group, HAVING count(DISTINCT …) = 2 (relational division)

Q12. Payment reconciliation: non-cancelled orders whose successful payments do not match the order total

WITH totals AS (
    SELECT order_id, sum(quantity * unit_price) AS due
    FROM order_items GROUP BY order_id
), paid AS (
    SELECT order_id, sum(amount) AS paid
    FROM payments WHERE status = 'succeeded' GROUP BY order_id
)
SELECT o.id, o.status, t.due, COALESCE(p.paid, 0) AS paid
FROM orders o
JOIN totals t    ON t.order_id = o.id
LEFT JOIN paid p ON p.order_id = o.id
WHERE o.status <> 'cancelled'
  AND t.due IS DISTINCT FROM p.paid
ORDER BY o.id;
  • Expected: orders 10 and 15 (pending, unpaid). Order 8’s split payment sums correctly, order 3’s failed attempt is ignored
  • Point: IS DISTINCT FROM is NULL-safe <> — a plain t.due <> p.paid would silently drop the unpaid orders

Hard

Q13. Each customer’s most recent order (one row per customer)

SELECT DISTINCT ON (o.user_id)
       o.user_id, u.full_name, o.id AS order_id, o.status, o.created_at::date
FROM orders o
JOIN users u ON u.id = o.user_id
ORDER BY o.user_id, o.created_at DESC;
  • Expected: 6 rows, e.g. Asha → order 15, Ben → order 13
  • Point: Postgres-only DISTINCT ON keeps the first row per key according to ORDER BY; portable version uses ROW_NUMBER() (Q14)

Q14. Top 2 best-selling products (by units) in each category

WITH units AS (
    SELECT p.category_id, p.name, sum(oi.quantity) AS units
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id AND o.status <> 'cancelled'
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category_id, p.id, p.name
), ranked AS (
    SELECT c.name AS category, u.name, u.units,
           ROW_NUMBER() OVER (PARTITION BY u.category_id ORDER BY u.units DESC, u.name) AS rn
    FROM units u
    JOIN categories c ON c.id = u.category_id
)
SELECT category, name, units
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;
  • Expected: 10 rows, e.g. Accessories → Wireless Mouse (5), Mechanical Keyboard (2)
  • Point: Window functions run after WHERE, so filter on rn in an outer query. Use RANK() instead of ROW_NUMBER() to keep ties (both laptops sold 2 units here)

Q15. Monthly revenue with running total and month-over-month change

WITH monthly AS (
    SELECT date_trunc('month', o.created_at)::date AS month,
           sum(oi.quantity * oi.unit_price)        AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY 1
)
SELECT month,
       revenue,
       sum(revenue) OVER w                                   AS running_total,
       lag(revenue) OVER w                                   AS prev_month,
       round(100.0 * (revenue - lag(revenue) OVER w)
             / NULLIF(lag(revenue) OVER w, 0), 1)            AS mom_pct
FROM monthly
WINDOW w AS (ORDER BY month)
ORDER BY month;
  • Expected: running total ends at 7425.50; January’s mom_pct is NULL (no previous month)
  • Point: NULLIF(x, 0) avoids division-by-zero errors

Q16. Category tree with full path and revenue rolled up to every ancestor

WITH RECURSIVE tree AS (
    SELECT id, name, name::text AS path, ARRAY[id] AS path_ids, 1 AS depth
    FROM categories
    WHERE parent_id IS NULL
  UNION ALL
    SELECT c.id, c.name, t.path || ' > ' || c.name, t.path_ids || c.id, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
), cat_revenue AS (
    SELECT p.category_id, sum(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN orders o   ON o.id = oi.order_id AND o.status IN ('paid','shipped','delivered')
    JOIN products p ON p.id = oi.product_id
    GROUP BY p.category_id
)
SELECT t.path,
       t.depth,
       COALESCE(own.revenue, 0)                    AS own_revenue,
       (SELECT COALESCE(sum(cr.revenue), 0)
          FROM tree d
          JOIN cat_revenue cr ON cr.category_id = d.id
         WHERE t.id = ANY (d.path_ids))            AS subtree_revenue
FROM tree t
LEFT JOIN cat_revenue own ON own.category_id = t.id
ORDER BY t.path;
  • Expected: Electronics subtree = 6240.50 (Laptops 4197.00 + Accessories 345.50 + Phones 1698.00); Computers = 4542.50; Home = 875.00
  • Point: Carry an array of ancestor IDs (path_ids) down the recursion; “is in my subtree” becomes my_id = ANY(descendant.path_ids)

Q17. For every user, their root referrer and how many hops away it is

WITH RECURSIVE chain AS (
    SELECT id AS user_id, id AS ancestor_id, 0 AS depth
    FROM users
  UNION ALL
    SELECT c.user_id, u.referred_by, c.depth + 1
    FROM chain c
    JOIN users u ON u.id = c.ancestor_id
    WHERE u.referred_by IS NOT NULL
)
SELECT DISTINCT ON (c.user_id)
       u.full_name, r.full_name AS root_referrer, c.depth AS hops
FROM chain c
JOIN users u ON u.id = c.user_id
JOIN users r ON r.id = c.ancestor_id
ORDER BY c.user_id, c.depth DESC;
  • Expected: Farid Khan → Asha Rao, 3 hops; users nobody referred are their own root with 0 hops

Q18. Days between consecutive orders per customer, and each customer’s average gap

WITH gaps AS (
    SELECT o.user_id,
           o.id,
           o.created_at::date AS order_date,
           o.created_at::date
             - lag(o.created_at::date) OVER (PARTITION BY o.user_id ORDER BY o.created_at) AS days_since_prev
    FROM orders o
    WHERE o.status <> 'cancelled'
)
SELECT u.full_name,
       count(*)                          AS orders,
       round(avg(days_since_prev), 1)    AS avg_gap_days   -- avg() ignores the NULL first gap
FROM gaps g
JOIN users u ON u.id = g.user_id
GROUP BY u.id, u.full_name
HAVING count(*) > 1
ORDER BY avg_gap_days;
  • Expected: Farid Khan has the shortest average gap (45.0 days), Ben Carter the longest (128.0)
  • Point: date - date returns an integer number of days in Postgres

Q19. Pareto analysis: each customer’s share of revenue, cumulative share and tier

WITH spend AS (
    SELECT o.user_id, sum(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status IN ('paid', 'shipped', 'delivered')
    GROUP BY o.user_id
)
SELECT u.full_name,
       s.total,
       round(100 * s.total / sum(s.total) OVER (), 1)                         AS pct_of_revenue,
       round(100 * sum(s.total) OVER (ORDER BY s.total DESC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                 / sum(s.total) OVER (), 1)                                   AS cumulative_pct,
       CASE NTILE(3) OVER (ORDER BY s.total DESC)
            WHEN 1 THEN 'gold' WHEN 2 THEN 'silver' ELSE 'bronze' END       AS tier
FROM spend s
JOIN users u ON u.id = s.user_id
ORDER BY s.total DESC;
  • Expected: Chen Wei and Eva Muller together make up 59.1% of revenue (gold)
  • Point: sum(x) OVER () = grand total on every row; ROWS frame makes the cumulative sum strictly row-by-row even with ties

Q20. Writes: restock via UPSERT, then place an order atomically

  • Part A: Restock from a supplier feed — existing SKUs add stock, new SKUs are inserted
BEGIN;
 
INSERT INTO products (sku, name, category_id, price, stock)
VALUES ('ACC-001', 'Wireless Mouse', 4, 25.00, 50),
       ('ACC-005', 'Webcam HD',      4, 59.00, 20)
ON CONFLICT (sku) DO UPDATE
   SET stock = products.stock + EXCLUDED.stock
RETURNING id, sku, stock, (xmax = 0) AS inserted;   -- xmax = 0 → row was inserted, not updated
 
ROLLBACK;   -- keep the practice data unchanged

  • Expected: ACC-001 stock 250, inserted = f; ACC-005 gets id 14 (not 13), inserted = t

  • Gotcha: ON CONFLICT still consumes an identity/sequence value for every proposed row, so gaps in IDs are normal

  • Part B: Gina buys 2 mice and 1 chef knife — lock the product rows, create the order and items, decrement stock, all in one statement inside a transaction

BEGIN;
 
WITH cart (product_id, quantity) AS (
    VALUES (3::bigint, 2), (9::bigint, 1)
), locked AS (
    SELECT p.id, p.price, c.quantity
    FROM products p
    JOIN cart c ON c.product_id = p.id
    WHERE p.is_active
    FOR UPDATE OF p                         -- block concurrent buyers of the same rows
), new_order AS (
    INSERT INTO orders (user_id, status) VALUES (7, 'pending')
    RETURNING id
), items AS (
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    SELECT n.id, l.id, l.quantity, l.price
    FROM new_order n CROSS JOIN locked l
    RETURNING product_id, quantity
)
UPDATE products p
SET    stock = p.stock - i.quantity         -- CHECK (stock >= 0) aborts the whole txn on oversell
FROM   items i
WHERE  p.id = i.product_id
RETURNING p.sku, p.stock AS remaining_stock;
 
ROLLBACK;   -- use COMMIT in real code
  • Expected: ACC-001 → 198, KIT-002 → 39
  • Point: All parts of a data-modifying CTE see the same snapshot and succeed or fail together; the CHECK constraint is the last line of defence against overselling

Interview Tips

  • Clarify First: Ask how to treat cancelled/pending orders, ties, NULLs and users with zero rows before writing SQL
  • Watch Fan-out: Joining two one-to-many tables then summing double-counts — aggregate in a CTE first (Q9, Q12)
  • Say the Postgres-specific Bit: DISTINCT ON, FILTER, ON CONFLICT, RETURNING, IS DISTINCT FROM, FETCH … WITH TIES
  • Check Edge Cases: Empty groups, NULL keys, division by zero, duplicate ranks

Summary

  • Schema: Seven tables covering the common interview shapes — one-to-many, many-to-many, self-references and trees
  • Easy: Filtering, sorting, GROUP BY/HAVING, NULL relabelling
  • Medium: Anti-joins, fan-out-safe aggregation, relational division, NULL-safe comparisons
  • Hard: DISTINCT ON, window ranking/running totals, recursive CTEs, UPSERT and atomic multi-table writes

Graph View

Backlinks