CODEWITHSUNDEEP
google-bigquery
user3600097
user3600097

Reputation: 35

Making one table with these two Queries

Hi I am calculating CRentention and I want to calculate the retention for 1day, 3 day and 7 days, but I have made three queries, can somebody please tell me how I can write thee in one query. Please help!

Upvotes: 1

Views: 71

Answers (1)

Jordan Tigani
Jordan Tigani

Reputation: 26617

The simple way to combine multiple queries is generally a join. If you take each of your queries and turn them into subqueries, then join by date, this should give you the results you want.

As in:

SELECT one_day.activity_date as activity_date,
    one_day.signed_up_users, one_day.logged_in_users, one_day.retention_pct,
    three_day.signed_up_users, three_day.logged_in_users, three_day.retention_pct,
    seven_day.signed_up_users, seven_day.logged_in_users, seven_day.retention_pct
FROM (SELECT ... ) as one_day
JOIN (SELECT ... ) as three_day
ON one_day.activity_date = three_day.activity_date
JOIN (SELECT ... ) as seven_day
ON one_day.activity_date = seven_day.activity_date

You also can use IF() clauses to do this in a non-joined fashion. You can create a column that is 1 if in the 1 day count and 0 otherwise, then compute the sum when you aggregate.

Upvotes: 1

Related Questions