Interview Query

Monthly Customer Report

3
Have you seen this question before?

Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January-December).

Example:

Input:

transactions table

Column Type
id INTEGER
user_id INTEGER
created_at DATETIME
product_id INTEGER
quantity INTEGER

products table

Column Type
id INTEGER
name VARCHAR
price FLOAT

users table

Column Type
id INTEGER
name VARCHAR
sex VARCHAR

Output:

Column Type
month INTEGER
num_customers INTEGER
num_orders INTEGER
order_amt INTEGER
Next question: Order Addresses
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query