sql_exploration_qa_example.sql

/* INTRODUCTION
--------------------------------------------------
Product recently launched a referral program, and their hoping to track progress through a dashboard.
Each referral link distributed gets tracked, and if used, both parties receive a beneift.

DB Tables Explored:
- public.referral_links
- public.referral_purchases
- public.users

Researcher: Colby Schrauth
Date: 2017-06-04
*/

/* EXPLORATION + QA */
--------------------------------------------------
-- 1. How many referral links have been distributed thus far, and are the link IDs unique?

-- QUERY
SELECT
  COUNT(link_id) AS "link_count"
, COUNT(DISTINCT link_id) AS "distinct_link_count"
FROM public.referral_links;

-- FINDINGS
-- As of 2017-06-04 there have been 125 links distributed, of which 100 are unique
-- Link IDs by themself are not unique

--2. Where are links being distributed most?

-- QUERY
SELECT
  a.user_region
, COUNT(b.link_id)
FROM public.users a
  LEFT JOIN public.referral_links b ON a.user_id = b.user_id
GROUP BY 1
ORDER BY 2 DESC;

-- FINDINGS
-- 100% of links have been distributed in the West Region

-- 3. How many links have been used in a purchase over the past 30-day period?

-- QUERY
SELECT
  SUM(total_usd) AS "referral_collections"
, COUNT(link_id) AS "num_links_in_purchase"
, COUNT(DISTINCT link_id) AS "num_unique_links_in_purchase"
FROM public.referral_purchases
WHERE purchase_date >= DATEADD(DAY, -30, GETDATE())

-- FINDINGS
-- There's been $1,000 collected through referral based purchases
-- It appears that a handful of links have been used more than once, as the counts above are different (50 vs. 40)

/* ACTION ITEMS
--------------------------------------------------
1. Ask Engineering what combination of columns make a referral link unique
2. Understand if, and why, Product has launched this offering in the West Region only
3. Ask Engineering and Product if a link is meant to be used more than once
*/