SQL Attribution Interview Questions: First Touch, Last Touch, and Conversion Windows

SQL attribution interview questions ask you to turn a business rule into a query over dated touchpoints and conversion events. Define the grain, find eligible touches for each conversion, then choose a deterministic first or last touch inside an explicit window. A user may convert more than once, so a user-level assignment can lose conversion paths.

Treat the examples here as rule-based reporting exercises: they assign credit according to a stated convention, but they do not prove that a channel caused a conversion.

Short answer: filter touches to each conversion's stated lookback window, then rank earliest and latest with a stable tie-breaker. Preserve unmatched conversions, define direct-traffic handling, and verify one output row per conversion.

Define the row grain before writing SQL

Start by saying what one output row represents. For a basic attribution table, it is one row per conversion, not one row per user, session, or touchpoint. That distinction matters when the same person buys twice, sees several ads, or converts after a later campaign interaction.

Ask which fields identify the entities. A useful simplified schema has conversions(conversion_id, user_id, converted_at, conversion_value) and touchpoints(touch_id, user_id, touched_at, channel). If identity can change across devices, the question needs a clearly defined identity key; do not assume that two devices belong to one person because their timestamps look similar.

Before coding, clarify whether the task credits impressions, clicks, sessions, or any marketing interaction; whether direct visits are eligible; how long before conversion to search; and what to do with repeated conversions. A “30-day window” can mean the 30×24 hours immediately before each conversion or 30 calendar dates in a declared timezone. Those definitions differ around midnight and daylight-saving changes.

First touch and last touch are ordering rules

Given a fixed eligible path, first-touch attribution selects its earliest event and last-touch attribution selects its latest. Snowplow's SQL tutorial demonstrates one practical implementation; it is an example, not a universal standard. In a window-function answer, ROW_NUMBER() makes the chosen row explicit. Add a unique event key after the timestamp so two events with identical times do not produce an arbitrary result.

For a conversion-specific path, the ranking key should include conversion_id. Partitioning only by user_id finds one first or last touch for the user's whole history. That is correct only if the requested output is explicitly user-level acquisition; it is usually wrong when conversions are counted individually.

Distinguish first touch inside a conversion window from first-ever acquisition. If asked where the user was originally acquired, do not limit the search to the 30 days before a later purchase. State the time scope and identity key.

These labels have product-specific meanings. Official product fact: Google Analytics Help lists data-driven attribution, paid-and-organic last click, and Google-paid-channels last click in its reports. First-click, linear, time-decay, and position-based models have been unavailable since November 2023, and last-click applies Google-specific Direct rules. A raw first/last-touch SQL exercise is an implementation convention, not a description of every platform. (Google Analytics: Get started with attribution)

Rule	Ordering inside each conversion's eligible path	What the output means
First touch	touched_at ASC, touch_id ASC	The earliest qualifying touch receives the full conversion credit.
Last touch	touched_at DESC, touch_id DESC	The latest qualifying touch receives the full conversion credit.
Last non-direct	Latest eligible non-direct touch; fall back only if the prompt says how	A product/reporting convention that differs from a plain last-touch query.

Worked example: one user converts twice

Suppose user u1 has these events, all times stored in UTC. A 30-day lookback includes the lower endpoint and excludes events at or after the conversion time.

Event	Time	Channel	Value
Touch t1	Apr 1, 09:00	Search	—
Touch t2	Apr 5, 12:00	Email	—
Touch t3	Apr 10, 08:00	Paid search	—
Touch t4	Apr 20, 08:00	Paid social	—
Conversion c1	Apr 21, 08:00	—	$120
Touch t5	Apr 22, 14:00	Email	—
Conversion c2	May 3, 08:00	—	$50

For c1, the first qualifying channel is Search and the last is Paid social. t5 occurs after c1, so it cannot receive credit for that conversion. For c2, a rolling 30-day window starts on Apr 3; t1 has aged out, making Email the first qualifying touch (t2) and Email the last (t5). Those labels come from two different Email events.

Notice what happens if you rank only by user: the query might assign both conversions to Search, or return only one conversion row. Either outcome loses the conversion-specific path. A conversion ID in the partition keeps each decision independent.

Add an explicit lookback window

The SQLite example uses normalized UTC text (YYYY-MM-DD HH:MM:SS) and returns first and last channel per conversion, including unmatched conversions. It includes Direct and does not implement last-non-direct attribution. The bound :lookback_days parameter is 30 in the fixture.

WITH ranked_touches AS (
    SELECT
        c.conversion_id,
        c.user_id,
        c.converted_at,
        c.conversion_value,
        t.touch_id,
        t.channel,
        ROW_NUMBER() OVER (
            PARTITION BY c.conversion_id
            ORDER BY t.touched_at ASC, t.touch_id ASC
        ) AS first_rank,
        ROW_NUMBER() OVER (
            PARTITION BY c.conversion_id
            ORDER BY t.touched_at DESC, t.touch_id DESC
        ) AS last_rank
    FROM conversions AS c
    LEFT JOIN touchpoints AS t
      ON t.user_id = c.user_id
     AND t.touched_at >= datetime(
           c.converted_at,
           printf('-%d days', :lookback_days)
         )
     AND t.touched_at < c.converted_at
)
SELECT
    conversion_id,
    user_id,
    converted_at,
    conversion_value,
    MAX(CASE WHEN first_rank = 1 THEN
        CASE WHEN touch_id IS NULL THEN 'Unattributed'
             ELSE COALESCE(channel, 'Unknown channel') END
    END) AS first_touch_channel,
    MAX(CASE WHEN last_rank = 1 THEN
        CASE WHEN touch_id IS NULL THEN 'Unattributed'
             ELSE COALESCE(channel, 'Unknown channel') END
    END) AS last_touch_channel
FROM ranked_touches
GROUP BY conversion_id, user_id, converted_at, conversion_value
ORDER BY converted_at, conversion_id;

For that fixture, the query returns c1 as Search/Paid social and c2 as Email/Email. An execution fixture also checks an exact 30-day boundary and an unmatched conversion; the expected labels are Affiliate/Affiliate and Unattributed/Unattributed, respectively.

The lower bound is inclusive, so a touch exactly 30 days before conversion qualifies. The upper bound is exclusive, so a touch recorded at or after conversion does not. If the prompt wants calendar-day windows, define a timezone and cast to dates deliberately instead of reusing this rolling interval.

Official product fact: Google Analytics defines its key-event lookback window as how far back a touchpoint can be to receive attribution credit. Its current documentation lists a 30-day default for acquisition events and a 90-day default for other key events, with other selectable values. Those defaults apply to Google Analytics settings; they are not a universal interview answer or a recommendation for every business. (Google Analytics: Select attribution settings)

Test the boundary cases that change the answer

The query is only as useful as its edge-case behavior. State the policy before changing the SQL; an interview prompt may intentionally leave one of these choices open so you can ask a clarifying question.

Case	Expected behavior under the example rule	Check
Touch exactly 30 days before conversion	Included	Compare against the inclusive lower bound.
Touch one second earlier	Excluded	Confirm the filter uses the rolling timestamp, not a date-only comparison.
Touch at conversion time or later	Excluded	Keep the < converted_at predicate.
Two touches share a timestamp	One selected by the declared touch_id tie-breaker	Ask whether an upstream sequence number is more meaningful.
Conversion has no eligible touch	Conversion stays in output as Unattributed	Keep the eligibility predicates in the LEFT JOIN condition.
Same user converts twice	Two output rows, each with its own eligible path	Partition by conversion_id.

There is a subtle join bug here: put the time-window conditions in a WHERE clause after a left join, and unmatched conversions are filtered out. Keep them in the ON clause if you want the unmatched conversion row preserved. Also deduplicate repeated event deliveries before ranking when the source can resend the same touch_id; otherwise a duplicate may alter counts or tie behavior.

Validate attribution without trusting the totals blindly

Run checks at conversion grain before grouping by channel. First, compare the number of distinct source conversion_id values with the final row count. Second, verify that every conversion appears once, including those with no path. Third, sum conversion value once from the conversion table and again from the attributed output; an increase usually signals a fanout join, while a decrease often means a conversion was dropped.

Then inspect selected rows manually: earliest eligible touch, latest eligible touch, a touch on each window boundary, a touch after conversion, and a user with two conversions. A query can return plausible channel totals while still using an incorrect join key. Joining only on user_id is not enough if the event log is not deduplicated or if the attribution policy requires ad, campaign, product, or session matching as well.

In SQLite, window functions such as row_number() assign a row number according to the ordering in the OVER clause. That makes the explicit tie-breaker material: if the ORDER BY fields are tied, the query has not stated a complete business rule. The SQLite window-function documentation describes ranking functions; its date/time documentation covers the date modifiers used in this example.

Separate assigned credit from causal impact

First- or last-touch SQL answers, “Which eligible interaction gets credit under this rule?” It does not answer, “Would the person have converted without this interaction?” People who receive or notice a channel can differ from people who do not, and channel timing may be related to intent.

Inference: When a case asks whether to shift budget, present attribution as one descriptive view and state what additional evidence would support an incremental claim—often a suitable randomized holdout or a defensible quasi-experimental design. Do not turn a channel's credited revenue into proof that the channel created that revenue. Google's description of data-driven attribution also distinguishes model-based credit allocation from a simple last-touch rule; the chosen reporting model should still be understood as an attribution method, not a universal causal guarantee.

Practice with five reported SQL question types

Question	Practice focus
Compute ad impression conversion rates	Match a conversion to an eligible impression within seven days, then preserve ads with zero attributed conversions.
Analyze Thirty-Day Ad Performance with SQL	State the reporting window, grain of intermediate tables, denominator, and attribution rule.
Determine Top Advertisers by Conversion Rate and CTR Analysis	Separate descriptive time comparisons from evidence of causal lift.
Retrieve Ad Metrics and Rates for Last 7 Days	Join event rows without multiplying impressions, then protect rate denominators.
Query conversion and retention with SQL windows	Deduplicate user events and define ordered conversion windows.

A concise way to explain your answer

Before presenting SQL, say: “I’ll return one row per conversion, match the same resolved user ID within the stated 30-day window, include its lower bound, exclude the conversion instant and later touches, and preserve unmatched conversions. I’ll rank first and last separately, using touch_id to break timestamp ties.”

Walk through one conversion, name a validation query, and describe what changes for last-non-direct or multi-touch credit. Clarify identity and window rules before coding when they are underspecified.

Before running a query, write down its grain, window, tie rule, and unmatched-row policy.

Sources and Further Reading

• Google Analytics Help: Get started with attribution — current report models and Google-specific Direct behavior.
• Google Analytics Help: Select attribution settings — current lookback-window definition and documented defaults.
• SQLite: Window Functions — ranking behavior and row_number().
• SQLite: Date And Time Functions — date modifiers used by the runnable example.
• Snowplow: First and Last Touch Attribution Models in SQL — a practitioner tutorial, not a universal standard.

Research checked September 12, 2026. Google Analytics settings are product-specific; the SQL conventions above are stated assumptions for an interview exercise.