rem ****************************************************************
rem Author: Shan Sun
rem Purpose: Scripts for demonstrating Solving business questions with SQL from different data sources
rem
rem ****************************************************************
rem Datasets: CRM data, Web activity data and orders data. Please note that the table and columns are fake.
/* Part 1 - CRM data to get Opportunities trend to validate tableau dashboard data */
TABLE Opportunity
(Opportunity_id INTEGER PRIMARY KEY,
Amount INTEGER NOT NULL,
Created_Date DATE NOT NULL)
TABLE Opportunity_Stage
(Updated_id INTEGER PRIMARY KEY,
Opportunity_id INTEGER NOT NULL,
Date_Stamp DATE NOT NULL,
OPPORTUNITY_TYPE VARCHAR(30) NOT NULL)
/* Task 1 Goal: Understand marketing pipeline trend and the % of marketing pipeline out of total pipeline. Solution: Pull marketing pipeline by year and by month since last year */
SELECT DATE_PART(Y,CREATED_DATE) AS YEAR,
DATE_PART(MONTH,CREATED)DATE) AS MONTH,
COUNT(DISTINCT O.OPPORTUNITY_ID),
SUM(AMOUNT) AS AMOUNT
FROM OPPORTUNITY O
LEFT JOIN OPPORTUNITY_SRAGE S ON O.OPPORTUNITY_ID = S.OPPORTUNITY_ID
WHERE OPPORTUNITY_TYPE LIKE '%Marketing%'
AND DATE_PART(Y,CREATED_DATE) > 2017
GROUP BY 1,2
ORDER BY YEAR DESC, MONTH DESC;
/* Part 2 - Web data to understand user behavior */
TABLE Impressions
(Impression_id INTEGER PRIMARY KEY,
userId INTEGER NOT NULL,
Timpestamp DATE NOT NULL)
TABLE Events
(Event_id INTEGER PRIMARY KEY,
userId INTEGER NOT NULL,
Timpestamp DATE NOT NULL)
/* Task 1 Goal: How many users were served an impression and fired event ID 101293 in September. Solution: Pulled users whose were served impression and only Event happened on Sept */
SELECT COUNT(DISTINCT I.USER_ID) AS USER_COUNT
FROM IMRPESSIONS I
INNER JOIN EVENTS E ON I.USER_ID = E.USER_ID
WHERE MONTH(E.TIMESTAMP) = 9 AND E.EVENT_ID = 101293
/* Task 2 Goal: How many users were served an impression per day in September. Solution: Pulled user whose were served one impression per day in Sept */
SELECT COUNT(USER_ID) FROM
(SELECT USER_ID, COUNT(IMP_COUNT) AS IMP_COUNT_SEPT FROM
(SELECT USER_ID, DAY(TIMESTAMP) AS DAY, COUNT(IMPRESSION_ID) AS IMP_COUNT
FROM IMPRESSIONS
WHERE MONTH(TIMESTAMP) = 9
GROUP BY USER_ID, DAY
HAVING COUNT(IMPRESSION_ID) = 1)
GROUP BY USER_ID
HAVING IMP_COUNT_SEPT = 30)
/* Part 3 - Orders data to understand customer purchase behavior */
TABLE ORDERS
(ORDERS_LINE_ID INTEGER PRIMARY KEY,
ORDER_ID INTEGER NOT NULL,
CUSTOMER_ID INTEGER NOT NULL,
CONTACT_EMAIL INTEGER NOT NULL,
ORDER_DATE DATE NOT NULL,
ITEM_ID INTEGER NOT NULL,
ITEM_NAME VARCHAR(30) NOT NULL,
Amount INTEGER NOT NULL)
TABLE CUSTOMERS
(CUSTOMER_ID INTEGER PRIMARY KEY,
CUSTOMER_NAME VARCHAR(30) NOT NULL)
TABLE ITEMS
(ITEM_ID INTEGER PRIMARY KEY,
ITEM_NAME VARCHAR(30) NOT NULL,
ITEM_TYPE VARCHAR(30) NOT NULL)
/* Task 1 Goal: Find out instrument customers that may need new instrument. Solution: Identify customers who purchased these instrument products between 2013 to 2015 and get their order details */
SELECT A.CUSTOMER_ID, B.CUSTOMER_NAME, A.ITEM_ID ,A.ITEM_NAME, A.ORDER_DATE, A.AMOUNT
FROM ORDERS A
LEFT JOIN CUSTOMERS B ON A.CUSTOMER_ID = B.CUSTOMER_ID
LEFT JOIN ITEMS C ON C.ITEM_ID=A.ITEM_ID
WHERE C.ITEM_TYPE = 'Instrument' AND YEAR(A.ORDER_DATE) BETWEEN 2013 AND 2015;