Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Duolingo
Attribute revenue across customer journey touchpoints (organic, paid search, social, email, referral) to understand true channel contribution and ROI.
Duolingo's growth team is arguing with finance over which marketing channels actually deserve credit for subscription revenue. The current last-click model hands 100% of the credit to the final touch (usually Email) — even when the user discovered the app through a Paid Search ad weeks earlier. Discovery channels look worthless; closing channels look heroic. Budget follows the distortion.
The fix is multi-touch attribution, computed two ways for comparison. Linear splits each subscription's revenue evenly across every touch in that customer's journey. Position-Based 40-20-40 gives 40% to the first touch (discovery), 40% to the last (conversion), and splits the remaining 20% across middle touches — encoding the belief that openings and closes matter most.
Each channel then gets conversions influenced, total attributed revenue, and share of revenue per model. Expect the models to disagree (Organic leads Linear, Referral leads Position-Based) — that disagreement is the finding. The recommendation must hold under all three lenses, especially the fate of Paid Search, smallest everywhere yet the journey-starter finance wants to cut.
Customer journey data with customer_id, touchpoint_channel, touchpoint_date, touchpoint_type (first/middle/last); conversion data with customer_id, subscription_date, subscription_value, cohort_month.
CREATE TABLE customer_journey (
journey_id INTEGER PRIMARY KEY,
customer_id INTEGER,
touchpoint_channel TEXT,
touchpoint_date DATE,
touchpoint_type TEXT
);
CREATE TABLE subscriptions (
subscription_id INTEGER PRIMARY KEY,
customer_id INTEGER,
subscription_date DATE,
subscription_value DECIMAL(10,2),
subscription_type TEXT
);Your target
Attribute revenue across customer journey touchpoints (organic, paid search, social, email, referral) to understand true channel contribution and ROI.
Loading the expected output…
WITH multi_touch_linear AS (
SELECT
s.customer_id,
cj.touchpoint_channel,
s.subscription_value,
COUNT(*) OVER (PARTITION BY s.customer_id) as channel_count,
ROUND(s.subscription_value / COUNT(*) OVER (PARTITION BY s.customer_id), 2) as attributed_revenue,
'Linear' as attribution_model
FROM subscriptions s
LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id
AND cj.touchpoint_date < s.subscription_date
WHERE cj.journey_id IS NOT NULL
),
multi_touch_position_based AS (
SELECT
s.customer_id,
cj.touchpoint_channel,
cj.touchpoint_type,
s.subscription_value,
CASE
WHEN cj.touchpoint_type = 'first' THEN ROUND(s.subscription_value * 0.40, 2)
WHEN cj.touchpoint_type = 'last' THEN ROUND(s.subscription_value * 0.40, 2)
WHEN cj.touchpoint_type = 'middle' THEN ROUND(s.subscription_value * 0.20 /
(COUNT(*) OVER (PARTITION BY s.customer_id, cj.touchpoint_type)), 2)
ELSE 0
END as attributed_revenue,
'Position-Based-40-20-40' as attribution_model
FROM subscriptions s
LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id
AND cj.touchpoint_date < s.subscription_date
WHERE cj.journey_id IS NOT NULL
),
channel_summary_linear AS (
SELECT
touchpoint_channel,
'Linear' as model,
COUNT(DISTINCT customer_id) as conversions,
ROUND(SUM(attributed_revenue), 2) as total_attributed_revenue,
ROUND(AVG(attributed_revenue), 2) as avg_per_conversion
FROM multi_touch_linear
WHERE touchpoint_channel IS NOT NULL
GROUP BY touchpoint_channel
),
channel_summary_position AS (
SELECT
touchpoint_channel,
'Position-Based-40-20-40' as model,
COUNT(DISTINCT customer_id) as conversions,
ROUND(SUM(attributed_revenue), 2) as total_attributed_revenue,
ROUND(AVG(attributed_revenue), 2) as avg_per_conversion
FROM multi_touch_position_based
WHERE touchpoint_channel IS NOT NULL
GROUP BY touchpoint_channel
)
SELECT
touchpoint_channel,
model,
conversions,
total_attributed_revenue,
avg_per_conversion,
ROUND(total_attributed_revenue / SUM(total_attributed_revenue) OVER (PARTITION BY model) * 100, 2) as revenue_percentage
FROM (
SELECT * FROM channel_summary_linear
UNION ALL
SELECT * FROM channel_summary_position
)
ORDER BY model, total_attributed_revenue DESC;
Step-by-step Solution:
Build customer_touchpoints CTE to aggregate journey data: count unique channels and total touchpoints per customer, concatenate journey path for visualization.
Create first_touch_attribution: extract earliest touchpoint channel for each customer (awareness phase attribution).
Create last_touch_attribution: extract latest touchpoint channel (conversion phase attribution).
Build multi_touch_linear: distribute subscription value equally across all touchpoints in the journey. This treats all channels equally but provides more balanced credit distribution than last-touch.
Create multi_touch_position_based with 40-20-40 weighting: first touch gets 40% (awareness), last touch gets 40% (conversion), middle touches share 20% (consideration). This reflects typical customer psychology.
Aggregate both models into channel_summary_linear and channel_summary_position to calculate total attributed revenue and conversion count per channel.
Compare models: Linear shows balanced contribution, Position-Based emphasizes first/last interactions. Calculate revenue_percentage to show each channel's share of total attributable revenue. This enables data-driven budget reallocation and accurate ROI measurement across channels.
Computing the reference output…
Last-click gives Email near-total credit, but multi-touch tells another story: Organic leads under Linear attribution (Rs.515, 25.2%) and Referral leads under Position-Based (Rs.479.20, 23.8%, best average per conversion at Rs.239.60). Email's true share is ~20-24%, not ~100% — shift budget toward the discovery channels (Paid Search, Social, Referral) that start the journeys Email merely closes.
What the interviewer asks next
“Linear says Organic, position-based says Referral, last-click says Email — three winners. Which number goes to finance?”
Strong answer covers: No single truth: present the range across models, explain what each model assumes about buyer behavior, and recommend budget shifts that hold under all three rather than picking a favorite.
“Paid Search is smallest under both models (9.75% / 11.87%) — finance wants it cut. Your call?”
Strong answer covers: Defending discovery: Paid Search starts journeys that Email closes; cutting the smallest-attributed channel starves the top of funnel — show first-touch share before agreeing to any cut.
“Customer 205 converts after just 2 Organic touches while 201 needs 3 across channels — does journey length break linear attribution?”
Strong answer covers: Dilution awareness: linear credit shrinks per touch as journeys lengthen, punishing long considered journeys; that is exactly why position-based exists, and when to prefer each.
“Customer 205 converts in 13 days on a Rs.149 Monthly plan; 201 takes 22 days for Rs.499 Annual — do faster journeys buy cheaper? What follows for spend?”
Strong answer covers: Journey-length economics: if quick converters buy small and slow journeys buy big, cutting upper-funnel spend to chase efficiency also kills Annual-plan pipeline — segment value by journey shape before reallocating.
“Referral tops position-based average (Rs.239.60) on a single conversion — would you scale spend on the back of n=1?”
Strong answer covers: Small-sample champions: the best average with the thinnest base is the most dangerous number in the table — demand replication (more referral conversions) before scaling a one-observation story.
“Email touches appear as both middle and last in the same journeys — does linear's even split misprice the assist vs the close?”
Strong answer covers: Role-aware credit: the same channel assisting and closing in one journey deserves role-split thinking, which is precisely the argument position-based makes — compare the two Email rows across models.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.