Please help me understand how SQL code works

I am doing SQL Intermediate course. I am currently on Code Challenge 6

I do understand the conditions in this code, I don’t understand how it works. When I was learning Python, I just read from top to bottom. I cannot do that here. I get the concept but lack true understanding of how it works.

What is the order of operations? What does SQL do first, second, third, etc?

Does this loop work the same as for loop in Python? Does it go first with January through the whole code, then February, etc?

I hope my question is clear.

 SELECT premium_users.user_id,
 	months.months,
  CASE
    WHEN (
      premium_users.purchase_date <= months.months
      )
      AND
      (
        premium_users.cancel_date >= months.months
        OR
        premium_users.cancel_date IS NULL
      )
    THEN 'active'
    ELSE 'not_active'
  END AS 'status'
FROM premium_users
CROSS JOIN months;

Thank you!

This SQL query is essentially selecting 3 columns — two that already exist and one that is being created with the CASE statement. Initially, you can think of it like this:

You are selecting the user_id column from the premium_users table, the months column from the months table, and your own newly-created status column.

The status column will contain what you told it to contain in your CASE statement logic. So here, an entry will say ‘active’ in the status column when:

  1. The value for purchase_date is less than or equal to the value of months; AND
  2. EITHER
    a. the value of cancel_date is greater than or equal to the value of months; OR
    b. there is no cancel date (cancel_date IS NULL)

image
If either or both of these conditions is not met, the entry will say not_active in that column.

A CASE statement is basically the same as an if-else statement in Python.

2 Likes

Hey! Thanks for your response! However, I still don’t understand it completely.

Is SQL’s order of operations like this:

  1. it selects all the tables
  2. it takes premium_user_id = 1
  3. it takes months = 1
  4. it goes through the case loop
  5. repeats STEPS 3 and 4 for all 12 months
  6. takes premium_user_id = 2
  7. repeats STEPS 3 - 5
  8. takes the next user and so on

The code in SQL is written in a way that it doesn’t show what happens and in what order. Or at least I see it like that. This order of operations is the thing I don’t get. Thanks!

image
A quick Google search for “SQL order of operations” or “SQL order of execution” will turn up numerous resources showing essentially this same order. I encourage you to check them out.

The one thing from your query that is missing from this list that is the CASE statement. These get executed at the at the same time as the statement they are part of. In your case, that is SELECT. In another query, your CASE statement might be somewhere else, like in the WHERE or GROUP BY statements, and be executed earlier than here.

So, your query would go in this order:

  • FROM table_1 CROSS JOIN table_2
  • SELECT column_1, column_2, column_3
    • However, since your third column, status, doesn’t exist, this is where your CASE statement is executed to create and then select that column using essentially steps 2 – 8 that you listed above.

Hope this helps break it down a little!

2 Likes

This is most helpful! Thanks a lot! :slight_smile:

1 Like