Case 5 — Marketing Attribution: First-Touch and Last-Touch

"From the touchpoints(user_id, touch_time, channel) table and conversions(user_id, conversion_time, revenue), build first-touch and last-touch attribution. Show revenue attributed to each channel."

This question tests window function fluency AND business judgment. The SQL is straightforward; the closing comment about why both models are wrong is what separates senior candidates from junior ones.

Schema

touchpoints(user_id, touch_time, channel)         -- e.g., 'google', 'facebook', 'email', 'direct'
conversions(user_id, conversion_time, revenue)

A touchpoint is any time a user interacts with a marketing channel before converting. A conversion is the purchase event.

First-touch attribution

The first channel that ever touched the user, before they converted.

WITH first_touch AS (
    SELECT
        user_id,
        FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY touch_time) AS first_channel
    FROM touchpoints
)
SELECT
    ft.first_channel AS channel,
    SUM(c.revenue) AS attributed_revenue,
    COUNT(DISTINCT c.user_id) AS attributed_conversions
FROM conversions c
JOIN (SELECT DISTINCT user_id, first_channel FROM first_touch) ft
    ON ft.user_id = c.user_id
GROUP BY ft.first_channel
ORDER BY attributed_revenue DESC;

Simpler alternative without FIRST_VALUE:

WITH first_touch AS (
    SELECT user_id, channel,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time) AS rn
    FROM touchpoints
)
SELECT
    ft.channel,
    SUM(c.revenue) AS attributed_revenue
FROM conversions c
JOIN first_touch ft ON ft.user_id = c.user_id AND ft.rn = 1
GROUP BY ft.channel;

Same answer. Some interviewers prefer FIRST_VALUE; others prefer ROW_NUMBER. Either is fine.

Last-touch attribution

The channel that touched them before the conversion (within some attribution window — typically 7 or 30 days). Without a window, "last touch before conversion" is the simplest:

WITH last_touch AS (
    SELECT
        c.user_id,
        c.conversion_time,
        c.revenue,
        (
            SELECT t.channel
            FROM touchpoints t
            WHERE t.user_id = c.user_id
              AND t.touch_time <= c.conversion_time
            ORDER BY t.touch_time DESC
            LIMIT 1
        ) AS last_channel
    FROM conversions c
)
SELECT
    last_channel AS channel,
    SUM(revenue) AS attributed_revenue
FROM last_touch
GROUP BY last_channel
ORDER BY attributed_revenue DESC;

The correlated subquery picks the most recent touchpoint per conversion. Alternative without correlation:

WITH ranked_touches AS (
    SELECT
        t.user_id,
        t.channel,
        t.touch_time,
        c.conversion_time,
        c.revenue,
        ROW_NUMBER() OVER (
            PARTITION BY c.user_id, c.conversion_time
            ORDER BY t.touch_time DESC
        ) AS rn
    FROM conversions c
    JOIN touchpoints t
        ON t.user_id = c.user_id
        AND t.touch_time <= c.conversion_time
)
SELECT channel, SUM(revenue) AS attributed_revenue
FROM ranked_touches
WHERE rn = 1
GROUP BY channel
ORDER BY attributed_revenue DESC;

Cleaner. The JOIN keeps only touches before the conversion, ROW_NUMBER picks the latest, GROUP BY aggregates.

Adding an attribution window

Add a date constraint to the JOIN:

AND t.touch_time >= c.conversion_time - INTERVAL '30 days'

Now only touchpoints in the 30 days before conversion are eligible. The cutoff depends on the business (B2C: 7-14 days; B2B: 30-90 days).

Why both first-touch and last-touch are wrong

This is the senior-analyst close. After producing the SQL:

You: "Both models have known problems. First-touch overweights awareness channels — people often discover via SEO/social months before converting, but those channels can't directly drive the purchase decision. Last-touch overweights conversion-driving channels — if someone always searches 'company name' before checkout, search gets disproportionate credit but isn't doing the real persuasion work. The honest answer for the business is multi-touch or data-driven attribution — weight each touchpoint by its incremental contribution. That's harder to compute and usually lives in a dedicated attribution tool (Heap, AppsFlyer, in-house ML model), not in pure SQL."

If the interviewer nods, follow up with: "For a SQL-only solution that's better than first/last, equal-weight attribution (divide revenue equally across all touchpoints in the window) is one step up — easy to compute and at least acknowledges the multi-touch nature."

WITH touches_per_conv AS (
    SELECT
        c.user_id, c.conversion_time, c.revenue, t.channel,
        COUNT(*) OVER (PARTITION BY c.user_id, c.conversion_time) AS total_touches
    FROM conversions c
    JOIN touchpoints t ON t.user_id = c.user_id
                      AND t.touch_time <= c.conversion_time
                      AND t.touch_time >= c.conversion_time - INTERVAL '30 days'
)
SELECT channel, SUM(revenue / total_touches) AS attributed_revenue
FROM touches_per_conv
GROUP BY channel
ORDER BY attributed_revenue DESC;

Common follow-ups

Follow-up 1: "What if a user converts multiple times?"

Each conversion is its own attribution event. The JOIN keys on (user_id, conversion_time), so multiple conversions are handled naturally. First-touch should reset per conversion or stay global — confirm with the interviewer.

Follow-up 2: "What if some channels have no touchpoint table records but we know they exist?"

Then your data is incomplete; flag it. Pure SQL can't attribute to a channel that doesn't appear in the events.

Follow-up 3: "How do we measure if the model itself is accurate?"

Two answers: (a) hold-out experiment — turn off a channel for a sample, measure conversions, compare to model prediction; (b) use a third-party MMM / attribution tool that handles incrementality. Pure SQL can compute the metric but not validate it.