SQL Explorer
Demo Environment
This environment resets nightly. Any data or connections you add are public.
SQL Explorer
New Query
Playground
Connections
Annotations
Logs
Favorites
select customers who made more than 10 purchases this month and histogram them into the cohort of their registration date by week
no such table: customers
Title
sqlite-sakila.db
chinook_1.db
player_1.db
Connection
select customers who made more than 10 purchases this month and histogram them into the cohort of their registration date by week
Description
SQL
WITH PurchasesThisMonth AS ( SELECT customer_id, COUNT(*) AS num_purchases FROM orders WHERE strftime('%Y-%m', purchase_date) = strftime('%Y-%m', 'now') GROUP BY customer_id HAVING COUNT(*) > 10 ), CustomerCohorts AS ( SELECT c.customer_id, c.registration_date, strftime('%Y-%W', c.registration_date) AS registration_week FROM customers c JOIN PurchasesThisMonth ptm ON c.customer_id = ptm.customer_id ) SELECT registration_week, COUNT(customer_id) AS num_customers FROM CustomerCohorts GROUP BY registration_week ORDER BY registration_week;
Assistant Example
Save & Run
Save Only
Download...
CSV
JSON
Show Schema
Hide Schema
SQL Assistant
Loading...
Assistant prompt
"Ask Assistant" to try and automatically fix the issue. The assistant is already aware of error messages & context.
Ask Assistant