michaelminter · October 28, 2020 16:47 · Oct 28, 2020
diff --git a/sql_template.sql b/sql_template.sql
@@ -0,0 +1,58 @@
+/* 
+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")