Calculate percentage between two columns in SQL Query as another column per day

I am trying to calculate the daily % split of the No_of_daily_installs.

Question:

Could someone explain how can I add a new column that represents the daily plit % of the No_of_daily_installs per LAT_type as %?

What I have now:

|-----------|-------------------|--------------------|-----------------------|
|Insall_Date|Lat_type           |No_of_daily_installs| RunningTotal_Installs |
|*---------*|*-----------------*|*------------------*|*---------------------*|
|2021-06-30 |Ad Tracking Enabled|   613              |21345                  |
|2021-06-30 |Limit Ad Tracking  |  3723              |74273                  |
|2021-06-29 |Limit Ad Tracking  |  3553              |70550                  |
|2021-06-29 |Ad Tracking Enabled|   480              |20732                  |
|2021-06-28 |Limit Ad Tracking  |  2869              |66997                  |
|2021-06-28 |Ad Tracking Enabled|   375              |20252                  |

What I would like to achieve:

|-----------|-------------------|--------------------|--------------|-----------------------
|Insall_Date|Lat_type           |No_of_daily_installs|%_of_daily_LAT| RunningTotal_Installs |
|*---------*|*-----------------*|*------------------*|*------------*|*---------------------*|
|2021-06-30 |Ad Tracking Enabled|   613              |0.15          |21345                  |
|2021-06-30 |Limit Ad Tracking  |  3723              |0.85          |74273                  |
|2021-06-29 |Limit Ad Tracking  |  3553              |0.80          |70550                  |
|2021-06-29 |Ad Tracking Enabled|   480              |0.20          |20732                  |
|2021-06-28 |Limit Ad Tracking  |  2869              |0.85          |66997                  |
|2021-06-28 |Ad Tracking Enabled|   375              |0.15          |20252                  |

My code so far:

WITH "Adtracking" (
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs"
) AS (
    SELECT
        to_date("created_at") date_install,
        CASE WHEN "tracking_limited" = '1' THEN
            'Limit Ad Tracking'
        ELSE
            'Ad Tracking Enabled'
        END AS "Ad Tracking",
        count("tracking_limited") AS "number_of_occurences"
    FROM
        TEMP_DB.DATA_LAKE.ADJUST_CSV_DATA
    WHERE
        TRUE
        AND "platform" = 'mobile_app'
        AND "activity_kind" = 'install'
        AND "os_name" = 'ios'
    GROUP BY
        1,
        2
    ORDER BY
        1,
        2
)
SELECT
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs",
    SUM("No_of_daily_installs") OVER (PARTITION BY "Lat_type" ORDER BY "Install_Date") AS "RunningTotal_Installs"
FROM
    "Adtracking"
WHERE
    "Install_Date" ILIKE '2021-06%'
GROUP BY
    1,
    2,
    3
ORDER BY
    1,
    2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The following divides the daily installs for each Lat type by the total /sum of daily installs for each install date and finally rounds that to 2 decimal places.

ROUND("No_of_daily_installs"/(SUM("No_of_daily_installs") OVER (PARTITION BY "Install_Date")),2) AS "%_of_daily_LAT"

You may try the complete example:

WITH "Adtracking" (
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs"
) AS (
    SELECT
        to_date("created_at") date_install,
        CASE WHEN "tracking_limited" = '1' THEN
            'Limit Ad Tracking'
        ELSE
            'Ad Tracking Enabled'
        END AS "Ad Tracking",
        count("tracking_limited") AS "number_of_occurences"
    FROM
        TEMP_DB.DATA_LAKE.ADJUST_CSV_DATA
    WHERE
        TRUE
        AND "platform" = 'mobile_app'
        AND "activity_kind" = 'install'
        AND "os_name" = 'ios'
    GROUP BY
        1,
        2
    ORDER BY
        1,
        2
)
SELECT
    "Install_Date",
    "Lat_type",
    "No_of_daily_installs",
-- modification begins
    ROUND("No_of_daily_installs"/(SUM("No_of_daily_installs") OVER (PARTITION BY "Install_Date")),2) AS "%_of_daily_LAT"
-- modification ends
    SUM("No_of_daily_installs") OVER (PARTITION BY "Lat_type" ORDER BY "Install_Date") AS "RunningTotal_Installs"
FROM
    "Adtracking"
WHERE
    "Install_Date" ILIKE '2021-06%'
GROUP BY
    1,
    2,
    3
ORDER BY
    1,
    2


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x