Drill 1 of 9 in CTEs & Reshaping Data
0

CTEs & Reshaping Data · CTE · MIN · date math

1. 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…