How does this statement tell us if a user is active or not active?

Frequently Asked Questions SQL FAQ
1

Question

In the context of this exercise, how does this statement tell us if a user is active or not_active?

Answer

This statement tells us if a user is active or not active based on conditions.

First, it will check whether the purchase date of the premium plan is earlier than or equal to the current date. If it is not, then there might have been some error or they are not yet signed up.
premium_users.purchase_date <= months.months

Second, it will check whether either of the following conditions is true:

If the user’s cancel date is the current date or is planned for the future, this means that they are still under the plan until their cancel date arrives,
premium_users.cancel_date >= months.months

Or, if the user has no cancel date yet, this means that they did not cancel nor plan to cancel,
premium_users.cancel_date is NULL.

If the first condition and either of the second conditions is true, this must mean they are still active. Otherwise, they are not_active.

8 Likes
2

Are you Assuming that if he is only active for the “whole month” of January that he is active in January and if not he is not active. This is fallacious reasoning in part cuz if someone is active for even one day during Jan I would consider them active during Jan not inactive.

9 Likes
3

I agree with board3405145664’s comment!

To rectify i think in the first condition of “premium_users.purchase_date <= months.month”, we could add an or “strftime(“m%”, premium_users.purchase_date) = strftime(”%m", months.month) to also include situations where some users were active for only part of the month or starting not exactly on the first date of month.

9 Likes
4

I agree with @board3405145664. I think the most important thing is to define what’s mean active.
If you select months.months for a datum mark. When （premium_users.purchase_date <= months.months, premium_users.cancel_date >= months.months OR premium_users.cancel_date is NULL）it’s true, it means active.
But if not like this. When (premium_users.purchase_date > months.months, premium_users.cancel_date >= months.months OR premium_users.cancel_date is NULL) it’s true, it also means active.

1 Like
5

I think the code is valid because months.months is just 1st date of month like Jan. 1st or Feb. 1st

2 Likes
6

I was under the impression that SQL could only compare INTEGER values as being < or >.
The months values are ‘text’ values. Is it possible to compare text values if one of the text values for months accidently says ‘january’?

7

I’m still confused by this. Firstly because the months table only lists 3 months sequentially within a specific 3 month period in 2017 so this solution would only be able to tell us if the user was active at 3 points in that time range. Secondly, is a simpler approach not to filter on whether or not the cancel date A: exists and B: has elapsed on a specific date if we want to find out if the users is currently active?

8

Can someone help me?

In code challenge 6 of Multiple Tables, why is

FROM premium_users
CROSS JOIN months

put after CASE query?

Naturally I would assume that, two tables first need to CROSS JOIN, then we can filter and group data by CASE. So why not CROSS JOIN before CASE?

Thanks! :slight_smile:

9

The CASE statement is part of the SELECT statement. It is evaluating what value to display for that row of data based on the date criteria.

10

Thanks! I kind get it now!

2 Likes
11

Have you tried looking up the months table with SELECT *?

Although the content is listed as a data type of text, it is structured as dates such as YYYY-MM-DD.
This is the same way the data is organized in the purchase_date and cancel_date columns, therefore SQL can compare the values because they are structured the same way!

12

Hi Im new here (and 5 years late to the party…)

Identified one user who comes up in all three months. The first month user is not active because they purchased their membership at the end of January (27th) so therefore not active on the 1st January.
The second month they are active because their membership was January 27th. The DID cancel on the 22nd Feb but again, the argument only asks whether they were active on the first of the month.
And, then NOT ACTIVE comes up in March as the cancellation was February 22nd.

I would say, as a newbie, that the user ID’s are overly long and complicated.
The conditions that have been set seem not very useful. Its difficult to imagine a scenario where someone’s activity on Day 1 of the month is useful.
This date format is difficult to read and it might have been helpful to go through a “re-naming the date” step. I believe this would be strftime ()? Is that right? I think someone else suggested that here.

ID PURCHASE CANCEL MONTH STATUS
|42038fb3-b538-4c25-9c76-cabf57c29e16 |2017-01-27 |2017-02-22 |2017-01-01 (January) | not_active|
|42038fb3-b538-4c25-9c76-cabf57c29e16 |2017-01-27 |2017-02-22 |2017-02-01 (February) | active|
|42038fb3-b538-4c25-9c76-cabf57c29e16 |2017-01-27 |2017-02-22 |2017-03-01 (March) | not_active|

1 Like