Interview Drills · CTE · MIN · date math
25. Days to first purchase
Concept: CTE · MIN · date mathshow
A CTE (WITH name AS (...)) names a subquery so you can build a query in readable steps. A typical pattern: step 1 computes something per customer, step 2 joins it back to the customers table.
Example (different data):
WITH first_post AS ( SELECT author_id, MIN(posted_at) AS first_at FROM posts GROUP BY author_id ) SELECT a.name, f.first_at FROM authors a JOIN first_post f ON f.author_id = a.id;
Your task
Activation analysis for a fintech growth team: how many days did each customer take to place their first completed order after signing up?
Return name, signup_date, first_order_date and days_to_first_order (whole days) for every customer with at least one completed order. Fastest first, ties by name.
Starting Postgres in your browser…
Checking your access…