Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

Practice Set: Window Functions and Joins

Explore SQL problems focused on window functions, joins, and ranking to build skills in handling running totals, leaderboards, referral analysis, and duplicate detection. Learn how to write correct queries for interview scenarios, understand common pitfalls, and validate output to ensure accurate insights in data science.

We'll cover the following...

This practice set covers six SQL problems involving ranking, running totals, and joins across multiple tables: a top-N leaderboard, a rolling daily metric, referral analysis using a self join and an additional join, cumulative spending, duplicate-account detection, and a three-part scorecard with progressively challenging follow-up questions. For each problem, predict the output or correct the query before reviewing the hints and complete solution.

The running example is a creator leaderboard, which must rank follower growth within each category without turning a many-category creator into duplicated follower counts. Every problem uses one SQLite schema. These are the tables and the columns the problems actually use:

  • users(user_id, signup_date, referred_by_user_id, email_hash, device_id): One row per account. referred_by_user_id points to the user who referred this one (Problem 3), and email_hash and device_id are the duplicate-account signals (Problem 5).

  • orders(order_id, user_id, amount, created_at): One row per order (Problems 3 and 4).

  • events(event_id, user_id, event_time): One row per activity event (Problem 2).

  • follows(follow_id, creator_id, user_id, followed_at): One row per follow. Both IDs are user IDs: creator_id is the account being followed, and user_id is the follower (Problems 1 and 6).

  • creator_categories(creator_id, category_id): One row per creator and category, so a creator can belong to several categories.

  • categories(category_id, category_name): One row per category.

A creator is simply a user with followers, so there is no separate creators table. Some problems use users and orders directly, but the grain check stays the same: name the row represented at every stage.

Six-table referral, commerce, and creator-category schema
Six-table referral, commerce, and creator-category schema

Problem 1: Top creators by follower growth

Scenario: Return the top three creators by follower growth this month within each category. Include tied creators at third place.

A near-correct answer aggregates follows, joins categories, computes a rank, then attempts to filter the rank in WHERE.

SELECT
  cc.category_id,
  f.creator_id,
  COUNT(*) AS gained_followers,
  RANK() OVER (
    PARTITION BY cc.category_id
    ORDER BY COUNT(*) DESC
  ) AS category_rank
FROM follows AS f
JOIN creator_categories AS cc
  ON cc.creator_id = f.creator_id
WHERE f.followed_at >= date('now', 'start of month')
  AND category_rank <= 3
GROUP BY cc.category_id, f.creator_id;
Near-correct query that filters a window rank in WHERE

The query fails because SQLite cannot filter a window result in the same query block’s WHERE clause.

Question: How would you rewrite this query to return the top three creators by follower growth in each category, including ties at third place?

Aggregate follower growth before ranking. The intended grain before the window is one row per category_id and creator_id.

Solution

The query below ranks creators within each category using one row per creator.

WITH monthly_gains AS (
  SELECT
    creator_id,
    COUNT(*) AS gained_followers
  FROM follows
  WHERE followed_at >= date('now', 'start of month')
  GROUP BY creator_id
),
ranked_creators AS (
  SELECT
    cat.category_name,
    mg.creator_id,
    mg.gained_followers,
    RANK() OVER (
      PARTITION BY cc.category_id
      ORDER BY mg.gained_followers DESC
    ) AS category_rank
  FROM monthly_gains AS mg
  JOIN creator_categories AS cc
    ON cc.creator_id = mg.creator_id
  JOIN categories AS cat
    ON cat.category_id = cc.category_id
)
SELECT
  category_name,
  creator_id,
  gained_followers,
  category_rank
FROM ranked_creators
WHERE category_rank <= 3
ORDER BY category_name, category_rank, creator_id;
Repaired query that ranks monthly gains in a CTE and filters in the outer query

Code explanation:

  • monthly_gains: One row per creator, counting the follows since the start of the month, so every creator has a single follower count before any join.

  • ranked_creators: Joins that count to creator_categories and categories, so a creator in two categories appears once per category with the same count. RANK() numbers the creators inside each category, and tied creators share a rank.

  • SELECT: Filters category_rank <= 3 in the outer query, because a window result cannot be filtered in the WHERE clause of the block that computes it.

The solution joins three tables in order: aggregated follows to creator_categories on creator_id, then creator_categories to categories on category_id, so the leaderboard shows each category by name. Both joins happen after monthly_gains has reached one row per creator, so a creator in two categories appears once per category with the same ...