/* 
CONTEXT: 
- add a brief description of why we need this query
RESULT EXPECTATION
- add a brief description of your expectations for the query result
ASSUMPTION:
- add assumption about business logic
- add assumption about data
*/

/* 
CONTEXT: 
- Our company wants to understand if COVID has any impact on sales in stores
around Chicago.
RESULT EXPECTATION
- This query returns total sales (in USD) for each of our stores in Chicago
every month before and after COVID, starting from 2019-03-01.
ASSUMPTION:
- Dates before 2020-03-01 are considered "Before COVID"
- Each transaction has a unique id, so we do not expect duplications
in our transaction table
- There are some spam transactions we have identified after COVID,
so we will filter these out
*/
SELECT 
  store_info.id,
  store_info.name AS store_name,
  DATE_FORMAT(transactions.date, "%Y-%m") AS transaction_month,
  SUM(transactions.total_amount) AS total_amount
FROM 
  transactions
LEFT JOIN 
  -- get all stores in Chicago
  (
    SELECT 
      id,
      name
    FROM 
      stores
    WHERE
      city = 'Chicago'
  ) AS store_info
ON 
  transactions.branch_id = store_info.id
WHERE
  transactions.date >= '2019-03-01'
  -- filter spam transactions
  AND transactions.id NOT IN 
    (
      SELECT 
        id 
      FROM 
        spam_transactions
    )
GROUP BY 
  store_info.id,
  store_info.name,
  DATE_FORMAT(transactions.date, "%Y-%m")