Skip to content

Instantly share code, notes, and snippets.

@ks--ks

ks--ks/InfluenceScore.sql

Created January 11, 2019 04:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ks--ks/93b632b83d5d88a0921a4526f4176d32 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ks--ks/93b632b83d5d88a0921a4526f4176d32 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ks--ks created this gist Jan 11, 2019.
    29 changes: 29 additions & 0 deletions InfluenceScore.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,29 @@
    , 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;