Patterns

Question 1

ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY event_time)

Answer 1

Find first/last event;

Question 2

LAG() / LEAD()

Answer 2

Session gap, churn detection;

Question 3

SUM(x) OVER(ORDER BY date)

Answer 3

Cumulative metrics;

Question 4

DENSE_RANK()

Answer 4

Ranking with ties (e.g. top product categories);

Question 5

COUNT(DISTINCT …)

Answer 5

Unique users, sessions;

Question 6

Nested CASE WHEN

Answer 6

Conditional metrics;

Question 8

“for each,” “per,” “every”

Answer 8

GROUP BY or PARTITION BY

Question 9

“total,” “count,” “average,” “sum”

Answer 9

Aggregate functions (COUNT(), AVG(), etc.)

Question 10

“most recent,” “latest,” “top 3,” “best”

Answer 10

ROW_NUMBER() or RANK() (Window Function)

Question 11

“and see their…” (e.g., posts and see their reactions)

Answer 11

LEFT JOIN

Question 12

“that also have” (e.g., posts that also have ‘heart’ reactions)

Answer 12

INNER JOIN or WHERE EXISTS

Question 13

“users with more than 10 posts” (filtering on an aggregate)

Answer 13

HAVING clause