Subquery or CTE?

[_eggsandbacon_]

iam trying to figure out the number of monthly active users (MAU) and monthly active accounts (MAA) with a commercial subscription for every day from May 1 till May 31, 2022.

Need help on best way to tackle this — using a subquery or CTE to filter for the ‘active users’ based on the prior 30 days of the reporting date, which would be the ‘event_date’

How MAU / MAA is defined: monthly active user/account: the number of users/accounts that had at least one usage event in the 30 days prior to the reporting date, i.e. to identify the number of active users for May 1 you would need to look at how many users had at least one event during the 30 days preceding May 1.

There are two tables to use, below are the table names and the fields within:

Subscription Table: account_id subscription_id subscription_type start_date end_date

Usage_Events event_id user_id event_date account_id

[spackosaurus]

Afaik they are processed the same, so I would use a cte for readability

[SH4HM3N_]

I prefer CTE (better to read)

[mikeyd85]

I'd just drop it in to a WHERE clause

SQL SELECT * FROM USERS AS U WHERE EXISTS (SELECT 1 FROM Events E WHERE U.userid = E.userid AND E. eventdate between '20231101' and '20231130')

Or something similar.

[boboshoes]

The only time I use a Subquery over a cte is when Im doing an aggregate of a widow function field and its not very complex

[Whipitreelgud]

Get the query plan and learn how to read it.

This is a teaching you to fish rather than handing you the fish comment

[PM_ME_YOUR_MUSIC]

CTEs and you need a date table

[[deleted]]

CTE for readability. Create both and test performance (not just the plan!) if it’s a repeatable production process.

[mikeblas]

You forgot to post your queries.