Metal model for SQL queries

These are some ways of thinking about applying the fucntion to make the SQL query for a problem.

1. Looking Across Rows

• LAG() → look back
• LEAD() → look forward

2. Accumulating

• SUM() OVER() → running total
• AVG() OVER() → running average

3. Ranking

• ROW_NUMBER() → unique order
• RANK() → gaps
• DENSE_RANK() → no gaps

4. Bucketing

• NTILE(4) → quartiles

5. Distribution

• PERCENT_RANK()
• CUME_DIST()

Lets think we are given a sales transactions dataset.

sales (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL
)

Can you show me running totals?

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales;

“Compare each order with the previous one.”

“Compare each order with the previous one.”

SELECT
  order_id,
  amount,
  LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS diff
FROM sales;

“I want top 3 customers per region.”

SELECT *
FROM (
  SELECT
    customer_id,
    region,
    SUM(amount) AS total_spent,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY customer_id, region
) t
WHERE rn <= 3;

“Your dataset has missing days”

SELECT
  order_date,
  LAG(order_date) OVER (ORDER BY order_date) AS prev_date,
  order_date - LAG(order_date) OVER (ORDER BY order_date) AS gap
FROM sales;

“Smooth the noise. Show me a 3-day moving average.”

SELECT
  order_date,
  amount,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

“Remove duplicates, keep latest”

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY order_date DESC
         ) AS rn
  FROM sales
) t
WHERE rn = 1;

“Who improved month over month?”

SELECT
  customer_id,
  order_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_amount,
  amount - LAG(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS growth
FROM sales;