Back Again

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Spotify

Problem

In transactions, a zero total_amount marks a voided sale and a negative one a refund, so only positive-amount rows are completed transactions. Each user with completed transactions sits in a tier by their completed count: equal counts share a tier, the highest count is tier 1 and each next lower count is the next tier. Return the username from users, the completed transaction count and the tier for everyone in the top two tiers.

Acquisition is expensive. These customers didn't need convincing twice.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

This is a tiered leaderboard with a business definition of 'completed' hiding inside it. Anyone can count rows per user. What separates candidates is two decisions. First, a zero total_amount is a voided sale and a negative one is a refund, so both come out before counting. On this data that drops andrew_k and brian from 14 to 13 and creates the second tier. Second, tied users share a tier and the next count takes the next number, so tier <= 2 keeps the 8 users at 14 and the 2 at 13. Number with gaps and the 13s land at tier 9: you ship 8 rows and silently lose the second tier.

Walk it through

Step 1: Define a completed transaction

Look at the sample rows. transaction_id 1335 has quantity 0 and total_amount 0.0, and 1603 has -45.0: a void and a refund. WHERE t.total_amount > 0 keeps only sales that actually completed. Skip it and two users are each one sale too high, which flattens the board back to two levels.

Step 2: Join and count per user

Inner-join users to transactions on user_id, then take COUNT(t.transaction_id) grouped by u.user_id, u.username. Group on the key, not just the label: the seed already holds both 'alice' and 'Alice'. Users with no completed sale never appear, which is right for a board of people who transacted.

Step 3: Number tiers without gaps

DENSE_RANK() OVER (ORDER BY txn_count DESC) gives 14 tier 1, 13 tier 2 and 12 tier 3. RANK() would give the 13s tier 9, because eight users sit above them. ROW_NUMBER() would invent an order among equals.

Step 4: Cut at the second tier

Put WHERE tier <= 2 in the outer query, because a window result cannot be filtered in the same SELECT that computes it. That keeps ten users: every leader plus both runners-up.

The solution

WITH completed_counts AS (
  SELECT u.username,
         COUNT(t.transaction_id) AS txn_count
  FROM users u
  INNER JOIN transactions t ON u.user_id = t.user_id
  WHERE t.total_amount > 0
  GROUP BY u.user_id, u.username
),
tiered AS (
  SELECT username,
         txn_count,
         DENSE_RANK() OVER (ORDER BY txn_count DESC) AS tier
  FROM completed_counts
)
SELECT username, txn_count, tier
FROM tiered
WHERE tier <= 2;

Numbering with gaps

RANK() over eight 14s, two 13s and five 12s gives 1 to the leaders and 9 to the two users at 13. tier <= 2 returns only the eight leaders.

Numbering without gaps

DENSE_RANK() gives 1, 2 and 3 to the three counts. tier <= 2 returns the eight leaders plus andrew_k and brian: ten rows.

Common follow-up questions

Common follow-up questions

• Return only the top tier, ties included. (Same query with `tier = 1`. Tests whether they keep the numbering instead of reaching for a `MAX` subquery.)
• Should a refund cancel the original purchase instead of simply not counting? (Probes netting refunds against sales by `product_id` versus filtering, and what a negative row represents.)
• Tier users by completed revenue, `SUM(total_amount)`, instead of count. (Checks the measure swap and how ties behave on a continuous value.)
• Scope the board to the last 90 days of `transaction_date`. (Adds a date predicate beside the amount filter and raises the question of users who fall off the board.)