Can you CROSS JOIN on more than two tables?

Hi,
In the first step of the exercise, it says to find the number of subscribers during the month of March but then somehow its using such syntax which would cover all the months less than or equal to 3 and more than or equal to 3 on the where statement.
If we are looking for the number of customers who got subscribed in March, why don’t we just write ‘start_month=3’ which displays the number 4 as the number of customers who got subscribed in March or during March which essentially mean same thing?

2 Likes

If we only look at subscribers with the “start_month” equal to 3, then we are only looking at subscribers who began their subscription in March. That would exclude anyone who began their subscription before March, but was still active during March, which is why we have the “start_month” set to less than or equal to 3 (before or during March) in the query.

The “end_month” follows similar logic. We have it set to more than or equal to 3 (during or after March) in the query. This makes sure to exclude anyone whose subscription ended before March.

1 Like

This also works

select *
from newspaper
cross join months
where months.month
between start_month and end_month;

3 Likes

Hi, I tried the following query and that seems to work.

SELECT MAX(subscribers)

FROM (SELECT month, COUNT(*) AS ‘subscribers’

FROM newspaper

CROSS JOIN months

WHERE start_month <= month

AND end_month >= month

GROUP BY month);

1 Like

Hi,
I guess this will not work because you use ‘subscribers’ BEFORE its definition (COUNT (*) AS ‘subscribers’).
To get max(subscribers) you may use the following code:

select month, count(*) as ‘subscr’
from newspaper
cross join months
where start_month <= month and end_month >= month
group by month
order by 2 desc
limit 1;

1 Like

OMG so tricky and hard

1 Like

In the 1st Checkpoint, I can’t understand how this query get customers who subscribed during March as the result in start_data and end_date includes other months as shown in fig below.

SELECT *
FROM newspaper
WHERE start_month <= 3
  AND end_month >=3;

Am I missing something? Or I just understand the question in a wrong way?

hi @fake.pshe
maybe that´s easier to understand if you write it as

WHERE month >= start_month
AND month <= end_month

that gives you exactly the same results. be only aware that this solution does not match 1:1 with codecademy solution and that bit will be highlighted as “wrong”

1 Like

How come we do not need to specify the table for our WHERE or SELECT clause? In previous exercises, whenever we use a join clause, our references to columns are always done as follows: table.column. For instance:

Screenshot 2023-08-08 at 1.19.26 PM

However, in our final exercise, we do not do this:

Screenshot 2023-08-08 at 1.20.04 PM

As you can see by the screenshot above, our select clause is not formatted as it previously was, and neither is our where clause. Instead of writing table.column, we just reference via column.

@lord_k good explanation but small nuisanced correction on the process steps - SELECT is performed first in the sense that it tells what SQL to look for and then FROM tells SQL ‘Look in those tables for what I asked you to SELECT’. In some processing languages that use SQL as base, unless you mentioned the columns in SELECT it might not necessarily compute the rest of the processes later - but this may differ from platform to platform.

@data1082842781 elegant and much easier to read for lay person and I learned a new keyword! Thank you!

@khaledhassan909 You should check out @h2theeather 's response above. It described it quite nicely.

yes, thank you. that is visually clear and simple to understand.

Thank you for clarifying. This objective was not clear to me until I read your post.

Hi all, I’m struggling to understand the WHERE logic from Checkpoint 3.

SELECT *
FROM newspaper
CROSS JOIN months
WHERE start_month <= month
AND end_month >= month;

When looking at the first customer in the results (id 21253, Vinnie Sagaser), his start_month was 1 and end_month was 5, so naturally what is returned is 1, 2, 3, 4, and 5 for month since he was actively subscribed from January through May… so in concept, I. understand this.

However, let’s take for example month 2… the value of 2 (month) is NOT less than or equal to 1 (start_month), and 2 is also NOT greater than or equal to 5 (end_month), so why is month 2 being returned as valid based on this logic?

Hopefully that makes sense.

The question asks, or the query… is whether start_month is less than or equal to month. Not whether month is less than or equal to the start_month.
If the start_month is 1, that is less than 2 from the months table.

1 Like