DB Exercises
0
Track Guide & Problems

PostgreSQL Window Functions Practice

Analyze sequential trends and partitioned cohorts without collapsing rows. Compute Month-over-Month (MoM) revenue acceleration, 3-day rolling moving averages, regional sales rankings, and user login gaps.

Track Size
14 Practice Problems
Engine
PostgreSQL 16 WASM (<5ms)
Grading
Automated Row-by-Row Checks
Start Problem 1: Month-over-Month Revenue Growth with LAG()

Core Syntax & Mechanics

Key patterns and rules practiced in this track.

Sequential Offsets with LAG() and LEAD()

Accesses values from previous (LAG) or subsequent (LEAD) rows within the same partition without self-joining. Essential for MoM growth, velocity, and time deltas.

Syntax Pattern
LAG(column, offset) OVER (PARTITION BY group_col ORDER BY sort_col)
Example
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_rev, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 2) AS growth_pct FROM monthly_revenue;

Dense Ranking with DENSE_RANK()

Assigns sequential rank integers without skipping numbers in the event of ties. Ideal for "Top N items per category" queries.

Syntax Pattern
DENSE_RANK() OVER (PARTITION BY partition_col ORDER BY rank_col DESC)
Example
SELECT rep_name, region, bookings, DENSE_RANK() OVER (PARTITION BY region ORDER BY bookings DESC) AS regional_rank FROM sales_reps;

Running Totals & Rolling Moving Averages

Accumulates values over an ordered sliding window frame without collapsing the individual transaction records.

Syntax Pattern
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
Example
SELECT entry_date, daily_net, SUM(daily_net) OVER (ORDER BY entry_date) AS running_balance FROM general_ledger;

Window Functions Exercises

Click any problem to open the interactive editor.

14 Problems
#1
Month-over-Month Revenue Growth with LAG()FREE

Time-series table showing month_date, revenue, prev_month_revenue, and growth_pct.

Practice
#2
Top 2 Sales Reps per Region with DENSE_RANK()FREE

Filtered table with rep_name, region, total_bookings, and regional_rank containing only rank 1 and 2 reps per region.

Practice
#3
Cumulative Cashflow Running TotalFREE

Time-series showing entry_date, daily_net, and running_balance.

Practice
#4
User Login Inactivity Gap with LEAD()PRO

User login history with next session date and gap in days.

Practice
#5
3-Day Rolling Sales Moving AveragePRO

Daily sales alongside 3-day rolling window average.

Practice
#6
Patient ICU Heart Rate Spike Detection with LAG()PRO

Patient telemetry readings showing previous heart rate and BPM delta.

Practice
#7
Card Transaction Velocity with Window COUNT()PRO

Transaction log showing the incremental card usage sequence number.

Practice
#8
Driver Earnings Quartile Segmentation with NTILE(4)PRO

Drivers segmented into 4 earnings quartiles (1 = top 25%, 4 = bottom 25%).

Practice
#9
Department Salary Percentile with PERCENT_RANK()PRO

Employee salary percentile within department on a 0.00 to 1.00 scale.

Practice
#10
Cohort Cumulative Expansion Revenue TrajectoryPRO

Monthly expansion MRR alongside cumulative cohort expansion total.

Practice
#11
Customer Initial vs Recent Order with FIRST_VALUE()PRO

Order history showing order amount alongside the customer first purchase value.

Practice
#12
Emergency Department Wait Time Quartiles with NTILE(4)PRO

ER patients categorized into 4 wait time quartiles.

Practice
#13
Real-Time Account Ledger Running BalancePRO

Ledger lines showing running balance per account.

Practice
#14
Driver Idle Turnaround Time Between Completed TripsPRO

Trip log showing preceding dropoff timestamp per driver.

Practice

Common Interview Mistakes

  • •Trying to filter window function outputs in `WHERE` instead of wrapping them in a subquery or CTE (window functions run after WHERE).
  • •Confusing `RANK()` (skips ranks on ties: 1, 2, 2, 4) with `DENSE_RANK()` (never skips: 1, 2, 2, 3).
  • •Omitting the `ORDER BY` clause inside `OVER()`, causing the aggregate to compute over the entire partition instead of producing a running total.

Frequently asked questions about Window Functions

Why can’t I use a Window Function in a WHERE clause?▸
Window functions execute in a later phase of query processing—specifically after WHERE, GROUP BY, and HAVING have finished. To filter on a window function result (such as `WHERE regional_rank <= 2`), you must wrap the query in a CTE or subquery.
What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?▸
ROW_NUMBER() assigns a unique number to every row (1, 2, 3, 4) even on ties. RANK() assigns the same number to ties and skips subsequent numbers (1, 2, 2, 4). DENSE_RANK() assigns the same number to ties but does not skip any ranks (1, 2, 2, 3).
How do you calculate a 7-day moving average in PostgreSQL?▸
Use `AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.

Practice Window Functions now

Run queries directly in your browser against realistic tables.

Open Problem 1