PowerUserAnalysis.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
)

, influence AS (
SELECT user_id 
	, recruit_score 
	, share_score 
	, days_share_score 
	, channel_share_score 
	, NTILE(100) OVER 
		(
		ORDER BY 
			recruit_score DESC
			, share_score DESC
			, days_share_score DESC
			, channel_share_score DESC
		)

FROM (
	SELECT a.user_id 
		-- , COUNT(DISTINCT a.item_name) AS item 
		, SUM(c.recruits)*COUNT(DISTINCT c.item_name) AS recruit_score 
		, COUNT(DISTINCT b.item_name)*COUNT(DISTINCT c.item_name) AS share_score 
		, SUM(b.days_shared)/COUNT(DISTINCT b.item_name)*COUNT(DISTINCT c.item_name) AS days_share_score 
		, SUM(channels_shared)/COUNT(DISTINCT b.item_name)*COUNT(DISTINCT c.item_name)::FLOAT AS channel_share_score
FROM action a 
LEFT JOIN shares b 
	ON a.user_id = b.user_id 
	AND a.item_name = b.item_name
LEFT JOIN recruits c 
	ON a.user_id = c.user_id 
	AND a.item_name = c.item_name
WHERE c.recruits > 0
GROUP BY 1 
)
GROUP BY 1, 2, 3, 4, 5
ORDER BY 2 DESC, 3 DESC, 4 DESC, 5 DESC
)

SELECT a.ntile
	, COUNT(DISTINCT a.user_id) AS users
FROM influence a 
GROUP BY 1
ORDER BY 1;