Drill 11 of 12 in Marketing questions
0

Interview Question Bank · COUNT(*) OVER (PARTITION BY) · fractional credit

11. Linear attribution across touches

HardMarketingQuestion #179

Concept: COUNT(*) OVER (PARTITION BY) · fractional creditshow

Linear attribution splits each conversion evenly across the touches that led to it: with n touches, each one gets 1/n of the conversion and 1/n of its revenue. Join the eligible touches to their conversion, get n with COUNT(*) OVER (PARTITION BY conversion), then sum the shares per channel. Write 1.0 / n, since 1 / n in integers is 0, and round only after summing.

Example (different data):

WITH shares AS (
  SELECT b.id, a.author, b.royalty,
    COUNT(*) OVER (PARTITION BY b.id) AS n
  FROM books b JOIN book_authors a ON a.book_id = b.id
)
SELECT author, ROUND(SUM(1.0 / n), 2) AS books_credited, ROUND(SUM(royalty / n), 2) AS royalty
FROM shares GROUP BY author;

Your task

Larkspur Roasters is testing linear attribution as an alternative to last-click. A touch counts if it happened at or before the conversion; conversions with no eligible touch are left out.

Split each conversion evenly across its customer's touches at or before converted_at. Return channel, credited_conversions (sum of the channel's shares, rounded to 2 decimals) and credited_revenue (sum of revenue shares, rounded to 2 decimals). Sort by credited_revenue descending, then channel.

Starting Postgres in your browser…

Checking your access…