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