Skip to content
Advertisement

SQL multi query

I need some help to do it right in one query (if it possible). (this is a theoretical example and I assume the presence of events in event_name(like registration/action etc)

I have 3 colums:

From this 3 columns we need to create new table with 4 new columns:

Result must be looks like this:

What I’ve done now: I’m use this toy example of my possible main table:

I’m trying to test some queries to create 4 new columns:

This is for column #1, we select month and year from timestamp where action is registration (as I guess), but I need to sum it for month (like 2019-11, 2019-12)

For column #2 we need to sum users with even_name registration in this month for every month, or.. we can trying for searching first time activity by user_id, but I don’t know how to do this.

Here is some thinks about it…

For column #3 we need to compare user_id with the event_name registration and last month event with any event of the second month so we get users who returned for the next month.

Any idea how to create this query?

This is how to calc column #4

Advertisement

Answer

I hope you will find the following answer helpful.

The first column is the extraction of year and month. The new_users column is the COUNT of the unique user ids when the action is ‘registration’ since the user can be duplicated from the JOIN as a result of taking multiple actions the following month. The returned_users column is the number of users who have an action in the next month from the registration. The returned_users column needs a DISTINCT clause since a user can have multiple actions during one month. The final column is the probability that you asked from the two previous columns.

The JOIN clause is a self-join to bring the users that had at least one action the next month of their registration.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement