跳到正文
DEV Community· Prashant Kumar Dey·· 4 小時前AI 評分39

取材自真實商業問題的 5 道 SQL 視窗函數練習

5 SQL Window-Function Exercises Taken From Real Business Problems

AI 導讀

文章整理了 5 道取材自分析師實際工作的 SQL 視窗函數練習，涵蓋客戶累計消費、距離上一張訂單的天數、每位客戶的首張訂單、月度收入變化，以及每月消費最高的客戶（包括並列情況）。練習提供提示，並可使用 Postgres、MySQL 8 或瀏覽器實驗室作答。

正文

Prashant Kumar Dey

Window functions are the point where most SQL learners stall. The syntax isn't hard; knowing when to reach for them is. These five exercises come from tasks analysts actually get. Try each one before reading the hint.

Use any Postgres or MySQL 8 database, or open a free browser lab at bartman.ai/labs/sql so there's nothing to install.

Setup 

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  amount      NUMERIC(10,2)
);

INSERT INTO orders VALUES
 (1, 101, '2026-01-04', 1200), (2, 102, '2026-01-09',  450),
 (3, 101, '2026-02-02',  800), (4, 103, '2026-02-11', 2300),
 (5, 102, '2026-03-15',  300), (6, 101, '2026-03-28',  950),
 (7, 104, '2026-03-30',  610), (8, 103, '2026-04-02', 1800);

1. Running revenue per customer

Task: For each order, show the customer's cumulative spend up to and including that order.

Hint
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)

2. Days since the previous order

Task: For each order, how many days since the same customer's previous order? First orders should show NULL.

Hint
order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)

3. Each customer's first order only

Task: Return one row per customer: their first order. No GROUP BY joins back to the table.

Hint
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) in a CTE, then filter = 1.

4. Month-over-month revenue change

Task: Total revenue per month, plus the percentage change from the previous month.

Hint
Aggregate by DATE_TRUNC('month', order_date) in a CTE, then LAG(total) OVER (ORDER BY month).

5. Top customer each month, ties included

Task: For each month, the customer(s) with the highest spend. If two tie, show both.

Hint
RANK(), not ROW_NUMBER(). That one word is the whole exercise.

Where to go next

Once these feel easy, the real test is applying them to a messy business question: churn by signup cohort, multi-touch attribution, or receivables ageing. Those are full projects rather than snippets. I put a few together on Bartman, for example subscription churn with CTEs and window functions and e-commerce funnel and cohort retention, where your queries are run and checked against the expected result.

Post your solution to #5 in the comments. I'm curious how many people reach for ROW_NUMBER() first.

Written with help from AI tools.

來源：DEV Community · dev.to

#教學/實踐