Katkat
Back

Compute this-year spend share of last-year whales

MetaData Scientist · Onsite
EasyData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

This question tests the ability to perform cohort analysis and time-based filtering to calculate the contribution of high-value segments across different fiscal years.

Problem Statement

You are provided with a table named ad_spend containing the following columns: user_id (int), spend (float), and transaction_date (date).

Define a "Whale" as any user who spent a total of $10,000 or more in the calendar year 2022.

Your task is to calculate the total spend of these 2022 Whales in the year 2023, and express this as a percentage of the total spend by all users in 2023.

Requirements

Write a query or script that returns a single decimal value representing this share.

Input Data Example

user_idspendtransaction_date
1120002022-05-10
25002022-06-15
120002023-01-20
350002023-02-10

Expected Logic

  1. Identify users where total spend in 2022 >= 10,000.
  2. Sum the spend of these identified users for the year 2023.
  3. Sum the spend of ALL users for the year 2023.
  4. Calculate: (Whale 2023 Spend / Total 2023 Spend) * 100.

Edge Cases

  • Users with no spend in 2023.
  • Users who spent 0 in 2022.
  • Years with no transaction data.

Hints

Hint 1

First, create a subquery or temporary table to filter for user IDs that meet the $10,000 threshold in the year 2022.

Hint 2

Use a CASE statement or a WHERE clause to isolate transactions specifically for the year 2023.

Hint 3

Consider using conditional aggregation (SUM(CASE WHEN...)) to compute both the numerator and denominator in a single pass over the 2023 data.

Follow-up questions

  • How would your query change if we defined a Whale as a user whose spend was in the top 1% of all users during 2022?
  • What impact would data skew have on this query if there are millions of transactions per day?
  • How would you modify this to calculate the spend share month-over-month for 2023?