Fetch Rewards·Data Scientist·Take-home Assignment·Intermediate

Intermediate

Jun 2026

Summary

Got a take-home style SQL problem from Fetch Rewards for a Data Scientist role. The whole thing was built around a beverage company dataset and whether Q2 2024 actually declined, which sounds straightforward until you realize how many ways you can mess up the grain or get tripped up by same-store logic.

Questions Asked (1)

Given a transactions, stores, and products schema for a beverage company, write a single ANSI SQL query that produces a quarter-over-quarter comparison table (Q2 2024 vs Q1 2024, and Q2 2024 vs Q2 2023) including overall revenue and unit metrics, same-store revenue change restricted to stores open for the full duration of both periods, and a decline flag triggered when both revenue and units drop more than 5%. The query must avoid grain duplication from joins and must compute period-level sums before deriving any ratios.

Product Analytics & MetricsData ModelingRoot Cause Analysis

Author's notes

This one took me longer than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and defining the three periods (Q1 2024, Q2 2024, Q2 2023) and the metrics needed. Use CTEs to pre-aggregate transactions at the correct grain (e.g., store-product-period) before joining to stores and products, ensuring no duplication. Then compute period-level sums and derive comparisons, applying the same-store filter and decline flag logic.

Pro tip: Explicitly state that you will aggregate transactions to the period-store-product level first, then join to dimension tables, to avoid fan-out. Also, mention that you will use conditional aggregation (CASE WHEN) to pivot periods into columns, which simplifies the comparison and avoids multiple self-joins.

1. Clarify schema and define periods

Identify the key columns in transactions, stores, and products, and define the exact date ranges for Q1 2024, Q2 2024, and Q2 2023. Confirm that 'same-store' means stores open for the full duration of both periods being compared.

2. Pre-aggregate transactions to avoid duplication

Create a CTE that aggregates transactions to the grain of store, product, and period (e.g., quarter), summing revenue and units. This ensures that subsequent joins to stores and products do not multiply rows.

3. Join dimensions and compute period-level metrics

Join the pre-aggregated CTE to stores and products to enrich with store and product attributes. Then, further aggregate to the period level (overall) and to the store level for same-store analysis, using conditional aggregation to pivot periods into columns.

4. Apply same-store filter and compute comparisons

For same-store revenue change, filter to stores that were open for the full duration of both periods in the comparison. Compute the percentage change for revenue and units between Q2 2024 and each comparison period.

5. Derive decline flag and final output

Create a decline flag that is true when both revenue and units drop by more than 5% for the given comparison. Structure the final output as a comparison table with columns for each metric and period pair.

Key Points to Mention

Use CTEs to pre-aggregate transactions at the correct grain before joining to dimension tables to prevent grain duplication.
Define same-store as stores with no gaps in operation during the full duration of both periods being compared, using store open/close dates.
Compute period-level sums (revenue, units) before calculating any ratios or percentage changes.
Use conditional aggregation (CASE WHEN) to pivot periods into columns for easy comparison.
Apply the decline flag logic: revenue change < -5% AND unit change < -5%.
Ensure the query is ANSI SQL compliant, avoiding vendor-specific functions where possible.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.