5. Monthly revenue

easy

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

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.



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 visa 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 snap easy -
197 Students who didn't finish all subjects snap easy -
196 User segments report snap hard -
193 Difference of replies by testing groups twitter medium
100%
192 Number of replies to the original tweet by group twitter medium
100%
190 Twitter campaign spend report twitter hard
50%
187 Comment distribution at Google Forum google easy
50%
186 Average comments per question google easy
50%
185 Top 5 non-English contents by watch time netflix medium
53%
184 Top 5 movies by watch time netflix medium
7%
183 Top 5 movies by number of watchers netflix easy
33%
181 Users who watched less than one hour of Netflix netflix easy
23%
174 Closed accounts social easy
18%
173 User Popularity Percentage social medium
5%
171 Binge watches tiktok easy
19%
170 Top 3 countries by average watch time tiktok easy
10%
169 Same day streams tiktok easy
29%
167 Top country in video streaming tiktok easy
24%
164 Orders distribution by brand walmart easy
22%
163 Average number of orders per brand walmart easy
36%
161 Biggest sales day ebay easy
19%
159 Transaction volume from suspended accounts ebay easy
51%
154 Rideshare completion rate lyft easy
22%
153 Project ROI dropbox easy
18%
149 fans by video category social easy
30%
146 Purchase by platform afterpay easy
13%
143 Customer purchase report afterpay easy
15%
134 Expensive departments robinhood medium
32%
133 Small departments robinhood easy
44%
132 Top 3 trending countries affirm medium
8%
130 Newly created accounts affirm easy
34%
128 Completion rate uber easy
23%
125 Active restaurants with fewer than 5 trips per city uber easy
14%
124 Active restaurants by city uber easy
36%
112 Top 3 customers amazon 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 amazon hard
11%
93 number of sales for each product this month amazon easy
19%
89 Top 5 artists in the US spotify medium
11%
86 Top song in the US spotify easy
19%
85 Top 4 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%