Table: rental
col_name | col_type --------------+-------------------------- rental_id | integer rental_ts | timestamp with time zone inventory_id | integer customer_id | smallint return_ts | timestamp with time zone staff_id | smallint
Question 7
Unique customers count by month
Instruction
- Write a query to return the total number of unique customers for each month
- Use
EXTRACT(YEAR from ts_field)andEXTRACT(MONTH from ts_field)to get year and month from a timestamp column. - The order of your results doesn't matter.
Sample results
year | mon | uu_cnt ------+-----+-------- 2020 | 1 | 123 2020 | 2 | 456 2020 | 3 | 789
Solution
postgresSELECT
EXTRACT(YEAR FROM rental_ts) AS year,
EXTRACT(MONTH FROM rental_ts) AS mon,
COUNT(DISTINCT customer_id) AS uu_cnt
FROM rental
GROUP BY year, mon;Explanation
This query is selecting data from a table called "rental" and extracting the year and month from a column called "rental_ts". It is also counting the number of distinct customer IDs and giving that count an alias "uu_cnt". The results are then grouped by year and month.
In summary, the query is counting the number of unique customers who rented something each month and displaying that count by year and month.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
5:00
Your results
Run your query to preview results here.