Skip to content

Instantly share code, notes, and snippets.

@savadev

savadev/PowerUserAnalysis.sql

Forked from ks--ks/PowerUserAnalysis.sql
Created October 25, 2024 03:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save savadev/82c5f05c55b0d4b39b6ce54038f19aa4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save savadev/82c5f05c55b0d4b39b6ce54038f19aa4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ks--ks ks--ks revised this gist Dec 16, 2020. 1 changed file with 16 additions and 4 deletions.
    20 changes: 16 additions & 4 deletions PowerUserAnalysis.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,7 +34,14 @@ SELECT user_id
    , 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)
    , NTILE(100) OVER
    (
    ORDER BY
    recruit_score DESC
    , share_score DESC
    , days_share_score DESC
    , channel_share_score DESC
    )

    FROM (
    SELECT a.user_id
    @@ -44,10 +51,15 @@ FROM (
    , 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
    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
    )
    GROUP BY 1, 2, 3, 4, 5
    ORDER BY 2 DESC, 3 DESC, 4 DESC, 5 DESC
    )
  2. @ks--ks ks--ks revised this gist Jul 6, 2020. No changes.
  3. @ks--ks ks--ks created this gist Apr 5, 2018.
    59 changes: 59 additions & 0 deletions PowerUserAnalysis.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    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;