I have the following dataset, and would like to create a DAU/MAU ratio for each day. I would want to create a beast mode that powers this number and can be used to generate a line chart at daily level.
You can use a case statement for this
SUM(CASE WHEN `category` = 'DAU' THEN `users` END) / SUM(CASE WHEN `category` = 'MAU' THEN `users` END)
Then make sure to graph based on your date field. You can change the aggregation function if you need something different.
Thanks @GrantSmith however, this will only give me a value when date is 30th Jan. How can I divide other dates with 30th Jan?
When should the Jan 30th date be used? For All dates in January or just some of them?
for every date of daily
Any luck here @GrantSmith ?
You can utilize a window function to pull the MAU for each month and divide your DAU by that number
MAX(CASE WHEN `category` = 'DAU' THEN `users` END) / MAX(MAX(CASE WHEN `category` = 'MAU' THEN `users` END) FIXED (BY LAST_DAY(`date`)))
GrantSmith's suggestion should work fantastic. Another option is to restructure the data in an ETL. Add a column to the data set that is MAU instead of having it as a dedicated row in the data source. Either way will work, just depends on what technology you are most comfortable with.
