PostgreSQL reference

WITH ranked AS (
  SELECT c.*,
         row_number() OVER (PARTITION BY lower(trim(email)) ORDER BY signup_date, customer_id) AS rn
  FROM customers c
  WHERE email IS NOT NULL
)
SELECT customer_id, first_name, last_name, email, signup_date
FROM ranked
WHERE rn = 1
ORDER BY customer_id
LIMIT 20;

SELECT lower(trim(email)) AS email_key,
       count(*) AS copies,
       array_agg(customer_id ORDER BY customer_id) AS customer_ids
FROM customers
WHERE email IS NOT NULL
GROUP BY lower(trim(email))
HAVING count(*) > 1
ORDER BY email_key;

SELECT DISTINCT ON (employee_id)
       employee_id, effective_date, base_salary, reason
FROM salary_history
ORDER BY employee_id, effective_date DESC
LIMIT 20;

WITH spend AS (
  SELECT c.region, c.customer_id, sum(oi.quantity * oi.unit_price - oi.discount_amount) AS revenue
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE c.region IS NOT NULL
  GROUP BY c.region, c.customer_id
), ranked AS (
  SELECT *, row_number() OVER (PARTITION BY region ORDER BY revenue DESC, customer_id) AS rn
  FROM spend
)
SELECT region, rn AS position, customer_id, round(revenue, 2) AS revenue
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

WITH monthly AS (
  SELECT date_trunc('month', o.ordered_at)::date AS month,
         sum(oi.quantity * oi.unit_price - oi.discount_amount) AS revenue
  FROM orders o
  JOIN order_items oi USING (order_id)
  WHERE o.status = 'delivered'
  GROUP BY month
)
SELECT month, round(revenue, 2) AS revenue,
       round(sum(revenue) OVER (PARTITION BY extract(year FROM month) ORDER BY month), 2) AS ytd_revenue
FROM monthly
ORDER BY month;

SELECT d::date AS day, count(t.ticket_id) AS tickets_opened
FROM generate_series(DATE '2025-01-01', DATE '2025-01-31', INTERVAL '1 day') AS g(d)
LEFT JOIN support_tickets t
  ON t.opened_at >= d AND t.opened_at < d + INTERVAL '1 day'
GROUP BY d
ORDER BY d;

SELECT channel,
       count(*) FILTER (WHERE ordered_at >= '2023-01-01' AND ordered_at < '2024-01-01') AS orders_2023,
       count(*) FILTER (WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01') AS orders_2024,
       count(*) FILTER (WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01') AS orders_2025
FROM orders
WHERE status <> 'cancelled'
GROUP BY channel
ORDER BY channel;

SELECT cat.category_name,
       round(sum(oi.quantity * oi.unit_price - oi.discount_amount), 2) AS revenue,
       round(100 * sum(oi.quantity * oi.unit_price - oi.discount_amount)
             / sum(sum(oi.quantity * oi.unit_price - oi.discount_amount)) OVER (), 2) AS pct_of_total
FROM order_items oi
JOIN products p USING (product_id)
JOIN categories cat ON cat.category_id = p.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;

SELECT device,
       count(*) AS sessions,
       count(*) FILTER (WHERE added_to_cart) AS carts,
       count(*) FILTER (WHERE purchased) AS purchases,
       round(100.0 * count(*) FILTER (WHERE purchased)
             / NULLIF(count(*) FILTER (WHERE added_to_cart), 0), 2) AS cart_to_purchase_pct
FROM fact_web_sessions
GROUP BY device
ORDER BY device;

WITH monthly AS (
  SELECT date_trunc('month', ordered_at)::date AS month, count(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT cur.month, cur.orders, prev.orders AS orders_last_year,
       round(100.0 * (cur.orders - prev.orders) / NULLIF(prev.orders, 0), 1) AS yoy_pct
FROM monthly cur
LEFT JOIN monthly prev ON prev.month = cur.month - INTERVAL '1 year'
WHERE cur.month >= '2024-01-01'
ORDER BY cur.month;

WITH daily AS (
  SELECT ordered_at::date AS day, count(*) AS orders
  FROM orders
  WHERE ordered_at >= '2025-11-01' AND ordered_at < '2026-01-01'
  GROUP BY day
)
SELECT day, orders,
       round(avg(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS moving_avg_7d
FROM daily
ORDER BY day;

WITH current_salary AS (
  SELECT DISTINCT ON (employee_id) employee_id, job_id, base_salary
  FROM salary_history
  ORDER BY employee_id, effective_date DESC
)
SELECT j.job_family,
       count(*) AS employees,
       percentile_cont(0.25) WITHIN GROUP (ORDER BY cs.base_salary) AS p25,
       percentile_cont(0.5)  WITHIN GROUP (ORDER BY cs.base_salary) AS median,
       percentile_cont(0.75) WITHIN GROUP (ORDER BY cs.base_salary) AS p75
FROM current_salary cs
JOIN jobs j ON j.job_id = cs.job_id
GROUP BY j.job_family
ORDER BY median DESC;

WITH active AS (
  SELECT DISTINCT customer_id,
         (extract(year FROM ordered_at) * 12 + extract(month FROM ordered_at) - 1)::int AS month_no
  FROM orders
  WHERE status <> 'cancelled'
), grouped AS (
  SELECT customer_id, month_no,
         month_no - row_number() OVER (PARTITION BY customer_id ORDER BY month_no) AS island
  FROM active
)
SELECT customer_id,
       make_date(min(month_no) / 12, min(month_no) % 12 + 1, 1) AS streak_start,
       count(*) AS months_in_a_row
FROM grouped
GROUP BY customer_id, island
HAVING count(*) >= 4
ORDER BY months_in_a_row DESC, customer_id
LIMIT 20;

SELECT c.customer_id, c.first_name, c.last_name, c.signup_date
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.signup_date, c.customer_id
LIMIT 20;

WITH items AS (
  SELECT order_id, sum(quantity * unit_price - discount_amount) AS items_value
  FROM order_items
  GROUP BY order_id
), paid AS (
  SELECT order_id, sum(amount) FILTER (WHERE status = 'captured') AS captured
  FROM payments
  GROUP BY order_id
)
SELECT o.order_id, o.status, i.items_value + o.shipping_fee AS order_value, COALESCE(p.captured, 0) AS captured
FROM orders o
JOIN items i USING (order_id)
LEFT JOIN paid p USING (order_id)
WHERE o.status IN ('pending', 'paid', 'shipped')
ORDER BY o.order_id
LIMIT 20;

WITH firsts AS (
  SELECT customer_id, date_trunc('month', min(ordered_at)) AS cohort
  FROM orders
  GROUP BY customer_id
), activity AS (
  SELECT DISTINCT o.customer_id, f.cohort,
         (extract(year FROM age(date_trunc('month', o.ordered_at), f.cohort)) * 12
          + extract(month FROM age(date_trunc('month', o.ordered_at), f.cohort)))::int AS month_offset
  FROM orders o
  JOIN firsts f USING (customer_id)
)
SELECT cohort::date AS cohort,
       count(*) FILTER (WHERE month_offset = 0) AS customers,
       count(*) FILTER (WHERE month_offset = 1) AS m1,
       count(*) FILTER (WHERE month_offset = 2) AS m2,
       count(*) FILTER (WHERE month_offset = 3) AS m3
FROM activity
WHERE cohort >= '2025-01-01'
GROUP BY cohort
ORDER BY cohort;

SELECT step, sessions, round(100.0 * sessions / first_value(sessions) OVER (ORDER BY step_no), 1) AS pct_of_sessions
FROM (
  SELECT 1 AS step_no, 'session' AS step, count(*) AS sessions FROM fact_web_sessions
  UNION ALL SELECT 2, 'product page', count(*) FILTER (WHERE reached_product_page) FROM fact_web_sessions
  UNION ALL SELECT 3, 'added to cart', count(*) FILTER (WHERE added_to_cart) FROM fact_web_sessions
  UNION ALL SELECT 4, 'checkout', count(*) FILTER (WHERE started_checkout) FROM fact_web_sessions
  UNION ALL SELECT 5, 'purchase', count(*) FILTER (WHERE purchased) FROM fact_web_sessions
) f
ORDER BY step_no;

SELECT t.account_id, t.posted_at, t.direction, t.amount,
       a.opening_balance
       + sum(CASE WHEN t.direction = 'credit' THEN t.amount ELSE -t.amount END)
           OVER (PARTITION BY t.account_id ORDER BY t.posted_at, t.transaction_id) AS balance_after
FROM transactions t
JOIN accounts a ON a.account_id = t.account_id
WHERE t.account_id = (SELECT min(account_id) FROM transactions)
ORDER BY t.posted_at, t.transaction_id
LIMIT 30;

SELECT je.entry_id, je.entry_number, je.status,
       sum(jl.debit) AS total_debit, sum(jl.credit) AS total_credit,
       sum(jl.debit) - sum(jl.credit) AS difference
FROM journal_entries je
JOIN journal_lines jl ON jl.entry_id = je.entry_id
GROUP BY je.entry_id, je.entry_number, je.status
HAVING sum(jl.debit) <> sum(jl.credit)
ORDER BY abs(sum(jl.debit) - sum(jl.credit)) DESC, je.entry_id;

SELECT a.employee_id, a.leave_id AS leave_a, a.status AS status_a, b.leave_id AS leave_b, b.status AS status_b,
       a.start_date AS a_start, a.end_date AS a_end, b.start_date AS b_start, b.end_date AS b_end
FROM leave_requests a
JOIN leave_requests b
  ON b.employee_id = a.employee_id
 AND a.leave_id < b.leave_id
 AND daterange(a.start_date, a.end_date, '[]') && daterange(b.start_date, b.end_date, '[]')
ORDER BY a.employee_id, a.leave_id, b.leave_id;

WITH RECURSIVE chain AS (
  SELECT employee_id, manager_id, 0 AS level, (first_name || ' ' || last_name) AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, c.level + 1, c.path || ' > ' || e.first_name || ' ' || e.last_name
  FROM employees e
  JOIN chain c ON e.manager_id = c.employee_id
)
SELECT employee_id, level, path
FROM chain
ORDER BY path
LIMIT 25;

WITH order_values AS (
  SELECT o.order_id, sum(oi.quantity * oi.unit_price - oi.discount_amount) + o.shipping_fee AS order_value
  FROM orders o
  JOIN order_items oi USING (order_id)
  GROUP BY o.order_id
)
SELECT width_bucket(order_value, 0, 3000, 12) AS bucket,
       (width_bucket(order_value, 0, 3000, 12) - 1) * 250 AS from_sar,
       count(*) AS orders
FROM order_values
GROUP BY bucket
ORDER BY bucket;

WITH actual AS (
  SELECT jl.account_code,
         sum(CASE WHEN g.account_type IN ('asset', 'expense') THEN jl.debit - jl.credit
                  ELSE jl.credit - jl.debit END) AS actual
  FROM journal_lines jl
  JOIN gl_accounts g ON g.account_code = jl.account_code
  JOIN journal_entries je ON je.entry_id = jl.entry_id AND je.status = 'posted'
  JOIN fiscal_periods fp ON fp.period_id = je.period_id AND fp.fiscal_year = 2025
  GROUP BY jl.account_code
), budget AS (
  SELECT account_code, sum(amount) AS budget
  FROM budgets
  WHERE fiscal_year = 2025
  GROUP BY account_code
)
SELECT b.account_code, g.account_name, b.budget, COALESCE(a.actual, 0) AS actual,
       COALESCE(a.actual, 0) - b.budget AS variance,
       round(100 * (COALESCE(a.actual, 0) - b.budget) / NULLIF(b.budget, 0), 1) AS variance_pct
FROM budget b
JOIN gl_accounts g ON g.account_code = b.account_code
LEFT JOIN actual a ON a.account_code = b.account_code
ORDER BY abs(COALESCE(a.actual, 0) - b.budget) DESC
LIMIT 15;