Skip to content

Instantly share code, notes, and snippets.

@SantonyChoi

SantonyChoi/analytics.md

Last active May 6, 2019 12:36
Show Gist options
  • Save SantonyChoi/debac8c5093b54963f3a6275fa60017a to your computer and use it in GitHub Desktop.
Save SantonyChoi/debac8c5093b54963f3a6275fa60017a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. SantonyChoi revised this gist May 6, 2019. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions analytics.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,21 +1,21 @@
    # Tool used
    - Postgresql

    ## How I solved
    # How I solved
    - Created a database and a table named "moloco_test".
    - Inserted all the data from the spreadsheet into the table.
    - Used Queries below for getting the answers

    # Queries
    Q1.
    ## Queries
    ### Q1.
    ```SQL
    SELECT site_id, COUNT(DISTINCT user_id) as unique_users
    FROM moloco_test
    WHERE country_id='BDV'
    GROUP BY site_id;
    ```

    Q2.
    ### Q2.
    ```SQL
    SELECT user_id, site_id, COUNT(user_id)
    FROM moloco_test
    @@ -24,7 +24,7 @@ GROUP BY user_id, site_id
    HAVING (COUNT(user_id)) > 10;
    ```

    Q3.
    ### Q3.
    ```SQL
    SELECT site_id, COUNT(site_id)
    FROM
    @@ -36,7 +36,7 @@ GROUP BY site_id
    ORDER BY count DESC;
    ```

    Q4.
    ### Q4.
    ```SQL
    SELECT COUNT(first_visits.user_id)
    FROM
  2. SantonyChoi created this gist May 6, 2019.
    60 changes: 60 additions & 0 deletions analytics.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,60 @@
    # Tool used
    - Postgresql

    ## How I solved
    - Created a database and a table named "moloco_test".
    - Inserted all the data from the spreadsheet into the table.
    - Used Queries below for getting the answers

    # Queries
    Q1.
    ```SQL
    SELECT site_id, COUNT(DISTINCT user_id) as unique_users
    FROM moloco_test
    WHERE country_id='BDV'
    GROUP BY site_id;
    ```

    Q2.
    ```SQL
    SELECT user_id, site_id, COUNT(user_id)
    FROM moloco_test
    WHERE ts >= '2019-02-03 00:00:00' AND ts <= '2019-02-04 23:59:59'
    GROUP BY user_id, site_id
    HAVING (COUNT(user_id)) > 10;
    ```

    Q3.
    ```SQL
    SELECT site_id, COUNT(site_id)
    FROM
    (SELECT user_id, MAX(ts)
    FROM moloco_test
    GROUP BY user_id) AS latest_info
    INNER JOIN moloco_test ON moloco_test.ts = latest_info.max AND moloco_test.user_id = latest_info.user_id
    GROUP BY site_id
    ORDER BY count DESC;
    ```

    Q4.
    ```SQL
    SELECT COUNT(first_visits.user_id)
    FROM
    (SELECT moloco_test.user_id, site_id as first_site, ts
    FROM
    (SELECT user_id, MIN(ts) as first_visit
    FROM moloco_test
    GROUP BY user_id) as first_visits
    INNER JOIN moloco_test
    ON moloco_test.user_id = first_visits.user_id AND ts = first_visit) as first_visits
    INNER JOIN
    (SELECT moloco_test.user_id, site_id as last_site, ts
    FROM
    (SELECT user_id, MAX(ts) as last_visit
    FROM moloco_test
    GROUP BY user_id) as last_visits
    INNER JOIN moloco_test
    ON moloco_test.user_id = last_visits.user_id AND ts = last_visit) as last_visits
    ON first_visits.user_id = last_visits.user_id
    WHERE first_site=last_site;
    ```