Instruction
- Write a query to return the total movie rental revenue for each month.
- For Postgres: you can use
EXTRACT(MONTH FROM colname)andEXTRACT(YEAR FROM colname)to extract month and year from a timestamp column. - For Python/Pandas: you can use pandas DatetimeIndex() to extract Month and Year
-
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
Expected results
Solution postgres
SELECT
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.