1565-UniqueOrdersAndCustomersPerMonth.sql

-- 1565. Unique Orders and Customers Per Month
-- Table: Orders
-- +---------------+---------+
-- | Column Name   | Type    |
-- +---------------+---------+
-- | order_id      | int     |
-- | order_date    | date    |
-- | customer_id   | int     |
-- | invoice       | int     |
-- +---------------+---------+
-- order_id is the column with unique values for this table.
-- This table contains information about the orders made by customer_id.
-- Write a solution to find the number of unique orders and the number of unique customers with invoices > $20 for each different month.
-- Return the result table sorted in any order.
-- The result format is in the following example.

-- Example 1:
-- Input: 
-- Orders table:
-- +----------+------------+-------------+------------+
-- | order_id | order_date | customer_id | invoice    |
-- +----------+------------+-------------+------------+
-- | 1        | 2020-09-15 | 1           | 30         |
-- | 2        | 2020-09-17 | 2           | 90         |
-- | 3        | 2020-10-06 | 3           | 20         |
-- | 4        | 2020-10-20 | 3           | 21         |
-- | 5        | 2020-11-10 | 1           | 10         |
-- | 6        | 2020-11-21 | 2           | 15         |
-- | 7        | 2020-12-01 | 4           | 55         |
-- | 8        | 2020-12-03 | 4           | 77         |
-- | 9        | 2021-01-07 | 3           | 31         |
-- | 10       | 2021-01-15 | 2           | 20         |
-- +----------+------------+-------------+------------+
-- Output: 
-- +---------+-------------+----------------+
-- | month   | order_count | customer_count |
-- +---------+-------------+----------------+
-- | 2020-09 | 2           | 2              |
-- | 2020-10 | 1           | 1              |
-- | 2020-12 | 2           | 1              |
-- | 2021-01 | 1           | 1              |
-- +---------+-------------+----------------+
-- Explanation: 
-- In September 2020 we have two orders from 2 different customers with invoices > $20.
-- In October 2020 we have two orders from 1 customer, and only one of the two orders has invoice > $20.
-- In November 2020 we have two orders from 2 different customers but invoices < $20, so we don't include that month.
-- In December 2020 we have two orders from 1 customer both with invoices > $20.
-- In January 2021 we have two orders from 2 different customers, but only one of them with invoice > $20.

-- Create table If Not Exists Orders (order_id int, order_date date, customer_id int, invoice int)
-- Truncate table Orders
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('1', '2020-09-15', '1', '30')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('2', '2020-09-17', '2', '90')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('3', '2020-10-06', '3', '20')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('4', '2020-10-20', '3', '21')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('5', '2020-11-10', '1', '10')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('6', '2020-11-21', '2', '15')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('7', '2020-12-01', '4', '55')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('8', '2020-12-03', '4', '77')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('9', '2021-01-07', '3', '31')
-- insert into Orders (order_id, order_date, customer_id, invoice) values ('10', '2021-01-15', '2', '20')

SELECT
    DATE_FORMAT(order_date ,"%Y-%m") AS month,
    COUNT(order_id) AS order_count,
    COUNT(DISTINCT customer_id) AS customer_count
FROM 
    Orders
WHERE 
    invoice > 20
GROUP BY 
    1