Bucket a date column into months with DATE_TRUNC and COUNT new customers per month. The acquisition curve every growth team watches.
Board update tomorrow. I need our customer acquisition curve: new customers by signup month. dim_customers has a signup_date. Truncate it to month, count signups, two columns: signup_month, new_customers. ORDER BY signup_month.
| Column | Type | Key |
|---|---|---|
| customer_id | INT | PK |
| TEXT | ||
| first_name | TEXT | |
| last_name | TEXT | |
| city | TEXT | |
| state | TEXT | |
| signup_date | TEXT | |
| acquisition_channel | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
dim_customers.signup_date is a TEXT date; cast it to a timestamp before truncating.
DATE_TRUNC('month', signup_date::timestamp) groups every signup into its month.
COUNT(*) per month, alias new_customers, ORDER BY signup_month ascending.