Instruction

  • Write a query to return the total movie rental revenue for each month.
  • For Postgres: you can use EXTRACT(MONTH FROM colname) and EXTRACT(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

Discuss


More COUNT, SUM, AVERAGE, MIN, MAX, GROUP BY, HAVING questions

ID Title Level FTPR
216 Highest spender gaming hard -
215 Monthly active users gaming hard -
214 D30 retention rate gaming hard -
209 Card issuer customers count fintech easy -
206 Search results recall by testing group search engine medium -
205 Search results recall search engine medium -
204 AB testing sanity check search engine easy -
202 Monthly active paid subscriptions subscription easy -
198 Average score per subject education easy -
197 Students who didn't finish all subjects education easy -
196 User segments report social hard -
193 Difference of replies by testing groups social medium
100%
192 Number of replies to the original tweet by group social medium
100%
190 Twitter campaign spend report advertising hard
50%
187 Comment distribution at Google Forum forum easy
50%
186 Average comments per question forum easy
50%
185 Top 5 non-English contents by watch time video streaming medium
53%
184 Top 5 movies by watch time netflix medium
7%
183 Top 5 movies by number of watchers video streaming easy
33%
181 Users who watched less than one hour of Netflix video streaming easy
23%
174 Closed accounts social easy
18%
173 User Popularity Percentage social medium
5%
171 Binge watches video streaming easy
19%
170 Top 3 countries by average watch time video streaming easy
10%
169 Same day streams video streaming easy
29%
167 Top country in video streaming video streaming easy
24%
164 Orders distribution by brand ecommerce easy
22%
163 Average number of orders per brand ecommerce easy
36%
161 Biggest sales day ecommerce easy
19%
159 Transaction volume from suspended accounts ecommerce easy
51%
154 Rideshare completion rate ride sharing easy
22%
153 Project ROI human resource easy
18%
149 fans by video category social easy
30%
146 Purchase by platform fintech easy
13%
143 Customer purchase report fintech easy
15%
134 Expensive departments human resource medium
32%
133 Small departments human resource easy
44%
132 Top 3 trending countries fintech medium
8%
130 Newly created accounts fintech easy
34%
128 Completion rate ride sharing easy
23%
125 Active restaurants with fewer than 5 trips per city food delivery easy
14%
124 Active restaurants by city food delivery easy
36%
112 Top 3 customers ecommerce easy
30%
107 Customer count by platform mobile medium
19%
106 Mobile vs. web mobile medium
9%
101 Average number of visits per user mobile easy
12%
98 Most popular user social easy
29%
97 Social influencer social easy
10%
96 Overall acceptance rate social easy
13%
95 Candidate products for subscription ecommerce hard
11%
93 number of sales for each product this month ecommerce easy
19%
89 Top 5 artists in the US music medium
11%
86 Top song in the US music easy
19%
85 Top 5 queries based on click through rate on new year's day search engine hard
6%
84 Click through rate on new year's day search engine medium
10%
82 The top search query on new year's day search engine easy
12%
81 How many people searched on new year's day search engine easy
24%
23 Average spend per customer in Feb 2020 easy
16%
21 Customer who rented the most medium
20%
20 Customer who spent the most medium
15%
19 Most productive actor medium
14%
18 Top film category easy
25%
10 Min and max spend easy
13%
9 Number of high spend customers by month medium
4%
8 Average customer spend by month easy
22%
7 Unique customers count by month easy
19%
6 Daily revenue in June, 2020 medium
11%