Funnel SQL: Cohort Signup Rate, Campaign KYC Submit Rates and Monthly Visitor Growth

A fintech company that serves businesses tracks its website acquisition funnel in three tables. A visitor arrives in a session, sometimes through a marketing campaign link; may sign up for an account during a session; and, once signed up, may submit Know Your Customer (KYC) verification documents. Using PostgreSQL, answer the four questions in the Parts below. The report lists the tables' columns and the four questions but gives no column types and does not define the cohorts or rates. The types below are assumptions, and the definitions the report leaves open are posed as clarifying questions. ### Input Tables **`sessions`**: one row per website session; `session_id` is unique. | Column | Type | Meaning | | --- | --- | --- | | `session_id` | TEXT | Unique key of the session | | `cookie_id` | TEXT | Browser cookie; identifies a visitor across sessions | | `start_timestamp` | TIMESTAMP | When the session started | | `marketing_campaign_url` | TEXT | URL of the marketing campaign that brought the session; assume NULL when the session did not come from a campaign | **`signup`**: one row per signup. The table contains only sessions that ended in a signup. | Column | Type | Meaning | | --- | --- | --- | | `signup_session_id` | TEXT | `session_id` of the session in which the signup happened | | `signup_date` | DATE | Date of the signup | | `account_id` | TEXT | Account created by the signup | **`merchant`**: one row per account. | Column | Type | Meaning | | --- | --- | --- | | `account_id` | TEXT | Account key | | `signup_date` | DATE | Date the account signed up | | `kyc_submit_date` | DATE | Date the account submitted KYC; assume NULL if it never did | | `lifetime_volume` | NUMERIC | Total payment volume the account has processed | ### Clarifying Questions - Is the 2023 cohort the visitors whose first session was in 2023, every visitor with a session in 2023, or the sessions that started in 2023? - Is the signup rate measured per visitor (`cookie_id`) or per session, and does a signup count only if it happens within some window after the first visit? - Is a signup credited to the campaign of the session in which it happened, or to an earlier campaign session of the same visitor? - Can one campaign appear under several URLs that differ only in tracking parameters, so that URLs need normalizing first? - Should sessions without a campaign be ranked as their own group or left out of the campaign rankings? - Does a KYC submission count at any time after signup, or only within a fixed window so that recent cohorts are comparable with older ones? ### Part 1 — Overall signup rate of the 2023 cohort Compute the overall signup rate of the 2023 cohort. ```hint One unit, top and bottom Decide whether the rate is per visitor or per session, and make the numerator count the same unit as the denominator. ``` #### What This Part Should Cover - An explicit definition of the cohort and of the rate's numerator and denominator - The join from `signup` back to `sessions`, without counting a visitor or a session twice - Ratio arithmetic that does not truncate to an integer ### Part 2 — Rank campaigns by KYC submit rate Rank the marketing campaigns by KYC submit rate, where rate = KYC submits / signups. ```hint Find the bridge The campaign is recorded on the session and the KYC date on the merchant row. Ask which table connects them. ``` #### What This Part Should Cover - Attribution of each signup to a campaign through its signup session - Keeping signups that never submitted KYC in the denominator - Deterministic ranking with ties, and caution about campaigns with few signups ### Part 3 — The five lowest-rate campaigns in each monthly cohort For each monthly cohort, find the five campaigns with the lowest KYC submit rate. ```hint Rank inside each month The ranking restarts for every cohort month, and the filter on rank can only be applied after the ranking is computed. ``` #### Clarifying Questions for this Part - If several campaigns tie at the fifth-lowest rate, should the query return exactly five rows or every tied campaign? - Should campaigns with very few signups in a month be left out of the ranking? #### What This Part Should Cover - The cohort-month definition and the per-month aggregation - A window ranking partitioned by month, filtered in an outer query, with an explicit tie rule - The fact that recent cohorts have had less time to submit KYC ### Part 4 — Month-over-month visitor growth per campaign Compute each campaign's month-over-month (MoM) visitor growth rate. ```hint Missing months A month in which a campaign had no visitors produces no row. Ask what a previous-row lookup then compares. ``` #### What This Part Should Cover - A visitor definition (distinct `cookie_id`) per campaign and month - The previous value taken from the previous calendar month, not merely the previous row - Growth when the previous month had zero visitors, and an incomplete current month ### What a Strong Answer Covers - Definitions agreed before each query and applied consistently across the four parts - The correct grain at every join (session, visitor, account), with no row multiplication - Correct ratio arithmetic: numeric casts, `NULLIF` guards and LEFT JOINs that keep non-converters - Window functions with explicit partitions, deterministic ordering and tie handling - The data caveats a stakeholder needs: cookie-based visitors, campaign attribution, cohort maturity and small denominators ### Follow-up Questions - How would you change Part 2 so that recent signup cohorts, which have had less time to submit KYC, are compared fairly with older ones? - How would you credit a campaign when a visitor first arrived through it but signed up later in a session with no campaign? - Part 1's rate can be computed per visitor or per session. Which would you report, and why can the two move in different directions? - How would you use `lifetime_volume` to judge whether a campaign with a low KYC submit rate is still valuable?