Queries, joins & window functions

-- INNER: rows present in both
SELECT u.name, o.total FROM users u JOIN orders o ON o.user_id = u.id;

-- LEFT: every user, NULLs where there is no order
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- users with NO orders — the "anti-join" pattern
SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL;
-- equivalently, and often clearer:
SELECT u.* FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- CROSS: cartesian product; useful for generating a calendar × category grid
SELECT d.day, c.id FROM generate_series('2025-01-01'::date,'2025-01-31',interval '1 day') d(day)
CROSS JOIN categories c;

-- WRONG (silently inner)          -- RIGHT
LEFT JOIN orders o ON o.user_id=u.id   LEFT JOIN orders o ON o.user_id=u.id AND o.status='paid'
WHERE o.status = 'paid'

-- most recent 3 orders per customer
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn <= 3;

SELECT
  day, revenue,
  SUM(revenue)  OVER (ORDER BY day)                                    AS running_total,
  AVG(revenue)  OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
  LAG(revenue)  OVER (ORDER BY day)                                    AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY day)                           AS delta,
  revenue::numeric / NULLIF(SUM(revenue) OVER (), 0)                   AS share_of_total,
  NTILE(4)      OVER (ORDER BY revenue)                                AS quartile
FROM daily_revenue;

-- find
SELECT email, COUNT(*), MIN(id) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- delete all but the oldest, safely
DELETE FROM users u
USING (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY created_at, id) AS rn
  FROM users
) d
WHERE u.id = d.id AND d.rn > 1;

-- then prevent recurrence
CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq ON users (lower(email));

-- org chart: everyone under a manager
WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 42     -- anchor
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.depth + 1                           -- recursive term
  FROM employees e JOIN subordinates s ON e.manager_id = s.id
  WHERE s.depth < 10                                                       -- cycle guard
)
SELECT * FROM subordinates;

-- monthly cohort retention
WITH first_seen AS (
  SELECT user_id, date_trunc('month', MIN(created_at)) AS cohort FROM events GROUP BY 1
),
activity AS (
  SELECT DISTINCT e.user_id, f.cohort, date_trunc('month', e.created_at) AS month
  FROM events e JOIN first_seen f USING (user_id)
)
SELECT cohort,
       (EXTRACT(YEAR FROM age(month, cohort)) * 12
        + EXTRACT(MONTH FROM age(month, cohort)))::int AS month_number,
       COUNT(DISTINCT user_id) AS users
FROM activity
GROUP BY 1, 2
ORDER BY 1, 2;

SELECT
  date_trunc('day', created_at)::date AS day,
  COUNT(*)                                          AS total,
  COUNT(*) FILTER (WHERE status = 'paid')           AS paid,        -- Postgres
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded,  -- portable
  ROUND(100.0 * COUNT(*) FILTER (WHERE status='paid') / NULLIF(COUNT(*),0), 1) AS paid_pct
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;