Teja Teja - 1 month ago 5
SQL Question

Percentage of users compared to yesterday without LAG analytic function

I would like to get the change percentage of user_counts when compared to yesterday without using analytic function as mysql doesn't support it. Just wondering what would be the best way to solve it.

Schema :-
User_Visits( VisitorID, UserID, Day )

What I have tried?

SELECT Day,
FROM (
( Day_User_Count - LAG( Day_User_Count, 1 ) OVER ( ORDER BY Day ) )/
Day_User_Count
)* 100 AS Percentage_Change
(
SELECT Day,
COUNT( DISTINCT UserID ) AS Day_User_Count
FROM User_Visits
GROUP BY Day
);

Answer

Give this a try.

SELECT 
    Day, COUNT(DISTINCT UserID) as Day_count, prv.Prev_Day_User_Count,
    (COUNT(DISTINCT UserID) - prv.Prev_Day_User_Count) 
        / prv.Prev_Day_User_Count * 100 AS Percentage_Change
FROM
    User_Visits UV
        INNER JOIN
    (SELECT 
        Day AS prev_day,
            COUNT(DISTINCT UserID) AS Prev_Day_User_Count
    FROM
        User_Visits
    GROUP BY Day) prv ON UV.day = DATE_ADD(prv.prev_day, INTERVAL 1 DAY)
GROUP BY Day;

Input:

UserID Day    
1   2016-10-20
1   2016-10-20
2   2016-10-20
3   2016-10-21
4   2016-10-21
5   2016-10-21
6   2016-10-22
1   2016-10-22
2   2016-10-23
3   2016-10-23
4   2016-10-23
5   2016-10-23
6   2016-10-24
7   2016-10-24

The output:

# Day, prev_day, Day_count, Prev_Day_User_Count, Percentage_Change
'2016-10-21', '2016-10-20', '3', '2', '50.0000'
'2016-10-22', '2016-10-21', '2', '3', '-33.3333'
'2016-10-23', '2016-10-22', '4', '2', '100.0000'
'2016-10-24', '2016-10-23', '2', '4', '-50.0000'

http://sqlfiddle.com/#!9/d3bcb