Divide numbers from same column based on criteria from another column

chetan_cricut

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.

GrantSmith

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.

chetan_cricut

Thanks @GrantSmith however, this will only give me a value when date is 30th Jan. How can I divide other dates with 30th Jan?

GrantSmith

When should the Jan 30th date be used? For All dates in January or just some of them?

chetan_cricut

for every date of daily

chetan_cricut

Any luck here @GrantSmith ?

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`)))

McSQL

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.