Master SQL Window Functions: From Basics to Advanced Scenarios

Hello everyone, I’m a keen reader sharing a useful SQL window‑function question for beginners.

Window functions are a must‑ask topic in SQL interviews; they test your ability to rank and aggregate rows without losing any rows.

Window function definition : specialized functions such as row_number(), rank(), dense_rank() and aggregate functions like sum(), avg(). They group rows by a specified dimension and order them, providing ranking or aggregation while preserving the original row count.

Window function expression : For example,

row_number() over([partition by dimension] order by dimension asc [desc])

. The partition by clause can be omitted; order by is mandatory.

row_number() over(partition by dimension order by dimension asc)

Basic practice : The task requires ranking rows by id and cat groups, ordering by time. The following script creates sample data and applies the window function.

# First step: construct data
insert into test values ('2020-10-02 12:30:45','A','AAA');
insert into test values ('2020-10-02 12:30:55','A','AAA');
insert into test values ('2020-10-02 14:39:45','A','BBB');
insert into test values ('2020-10-02 14:40:55','A','BBB');
insert into test values ('2020-10-02 15:30:05','A','AAA');
insert into test values ('2020-10-02 16:30:45','B','AAA');
insert into test values ('2020-10-02 17:04:45','B','BBB');

# Query
select
time,
id,
cat,
row_number() over(partition by id,cat order by time asc) as rnk
from test
order by time asc;

The result image (omitted) shows the correct ranking.

Advanced practice : The problem description is:

First column is event time, second column is user id, third column is event group, fourth column is the desired ranking. Reset the count when the event group or user id changes.

To solve this, we create an auxiliary column that changes whenever id or cat changes, then apply the window function.

# 1. Construct data (same as basic example)
with temp1 as (
  select time, id, category,
         concat_ws('-', id, category) as add_col,
         row_number() over(order by time asc) as order_rnk
  from test
),
temp2 as (
  select time, id, category, add_col, order_rnk,
         order_rnk - lag(order_rnk, 1, order_rnk-1) over (partition by add_col order by time asc) as order_rnk_lag1
  from temp1
),
temp3 as (
  select time, id, category,
         row_number() over(partition by concat(add_col, order_rnk_lag1) order by time asc) as rnk
  from temp2
)
select * from temp3;

The accompanying images illustrate the data, the lag calculation, and the final ranking.

Use these scripts to practice window functions and improve your SQL interview skills.