Question 5
Instruction
EXTRACT(MONTH FROM colname) and EXTRACT(YEAR FROM colname) to extract month and year from a timestamp column.df['year'] = pd.DatetimeIndex(df['InsertedDate']).year
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 | rev ------+-----+---------- 2020 | 1 | 123.45 2020 | 2 | 234.56 2020 | 3 | 345.67
Solution
postgresSELECT
EXTRACT(YEAR FROM payment_ts) AS year,
EXTRACT(MONTH FROM payment_ts) AS mon,
SUM(amount) as rev
FROM payment
GROUP BY year, mon
ORDER BY year, mon;Explanation
This query is selecting data from a table called "payment" and is asking Postgres to extract the year and month from a column called "payment_ts" (which likely stands for payment timestamp). It is also asking Postgres to sum the values in a column called "amount" and to alias that sum as "rev".
The query is then grouping the data by year and month, and ordering the results by year and month.
Essentially, this query is asking Postgres to provide a breakdown of payments by year and month, with the total revenue for each month. This could be useful for identifying trends in revenue 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.