PowerUser_Step1

ActionShares.sql

WITH

recruits AS (
SELECT recruiter_id AS user_id
	, item_name
	, COUNT(DISTINCT recruited_id) AS recruits
FROM recruit_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2
)

, shares AS (
SELECT user_id
	, item_name
	, COUNT(DISTINCT created_at::DATE) AS days_shared
	, COUNT(DISTINCT location) AS channels_shared
FROM share_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2
)

, action AS (
SELECT user_id
	, created_at
	, item_name
FROM action_table
WHERE created_at::DATE BETWEEN '2017-01-01'-180 AND '2017-01-08'
GROUP BY 1, 2, 3
)