Question 8
Instruction
EXTRACT(YEAR from ts_field) and EXTRACT(MONTH from ts_field) to get year and month from a timestamp column.Table: payment
Movie rental payment transactions table
col_name | col_type --------------+-------------------------- payment_id | integer customer_id | smallint staff_id | smallint rental_id | integer amount | numeric payment_ts | timestamp with time zone
Sample results
year mon avg_spend 2020 2 3.2543037974683544 2020 5 9.1301559454191033
Solution
postgresSELECT
EXTRACT(YEAR FROM payment_ts) AS year,
EXTRACT(MONTH FROM payment_ts) AS mon,
SUM(amount)/COUNT(DISTINCT customer_id) AS avg_spend
FROM payment
GROUP BY year, mon
ORDER BY year, mon;Explanation
This query is selecting data from the 'payment' table in a PostgreSQL database. The data selected includes the year and month of each payment transaction, as well as the average amount spent per customer during that time period.
The EXTRACT function is used to extract the year and month from the payment timestamp column ('payment_ts'). The SUM function is used to calculate the total amount spent during each month, and the COUNT and DISTINCT functions are used together to count the number of unique customers who made payments during that month. The result is an average spend per customer for each month in the dataset.
Finally, the results are grouped by year and month and ordered in ascending order by year and month. This allows the data analyst to easily see trends in average spend over time.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
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
Run your query to preview results here.