Rank Campaigns by KYC Submission Rate

Given the PostgreSQL tables: ```sql sessions ( session_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, started_at TIMESTAMP NOT NULL, marketing_campaign_url TEXT ) signups ( signup_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, session_id BIGINT NOT NULL, signup_at TIMESTAMP NOT NULL ) merchants ( merchant_id BIGINT PRIMARY KEY, signup_id BIGINT UNIQUE NOT NULL, kyc_submitted_at TIMESTAMP ) ``` Rank campaigns by KYC submission rate among signups attributed to a session in calendar year 2023. A signup is attributed to its referenced session, and it counts as a KYC submission when a merchant row for that signup has non-null `kyc_submitted_at` at or after `signup_at`. Return: ```text marketing_campaign_url, signup_count, kyc_submit_count, kyc_submit_rate, campaign_rank ``` ### Constraints and Clarifications - Include only sessions with a non-null `marketing_campaign_url` and `started_at >= '2023-01-01'` and `< '2024-01-01'`. - Count distinct `signup_id` values in both metric components. - Rank higher rates first using dense ranks; campaigns with equal rates share a rank. - Sort by `campaign_rank`, then `marketing_campaign_url` ascending. - A campaign with signups but no qualifying KYC submission has rate zero. ```hint Aggregate rates before ranking Join at the signup grain, compute one rate per campaign with conditional distinct counts, then apply the ranking window to those campaign rows. ``` ### Evaluation Focus - Correct sessions-to-signups-to-merchants joins without double counting. - A signup-grain denominator and conditional KYC numerator. - Numeric rate calculation and deterministic ranking output. - Correct 2023 session cohort boundary. ### Extension How would you add a minimum-sample threshold without biasing the rate calculation itself?