Number of Accounts That Did Not Stream (MYSQL via Leetcode)

Question

Question

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| start_date  | date |
| end_date    | date |
+-------------+------+

In table Subscriptions, account_id is the primary key column for this table. Each row of this table indicates the start and end dates of an account's subscription. Note that always start_date < end_date.

+-------------+------+
| Column Name | Type |
+-------------+------+
| session_id  | int  |
| account_id  | int  |
| stream_date | date |
+-------------+------+

In table streams, session_id is the primary key column for this table. account_id is a foreign key from the Subscriptions table. Each row of this table contains information about the account and the date associated with a stream session.

Write an SQL query to report the number of accounts that bought a subscription in 2021 but did not have any stream session.

Can someone please help me to identify what is wrong with my following code? I don't wish to use year = 2021 but want to use exact dates. Please help.

with temp1 as
(
select account_id
from Subscriptions sb
where start_date >= 2021-01-01 and end_date <= 2021-12-31
and account_id not in (select distinct(account_id)
                              from streams
                              where stream_date >= 2021-01-01 and stream_date <= 2021-12-31 ))
select count(account_id) as accounts_count
from temp1;

Answer 1

Dates have to be in single quotes. more information about quotes

like

with temp1 as
(
select account_id
from Subscriptions sb
where start_date >= '2021-01-01' and end_date <= '2021-12-31'
and account_id not in (select distinct(account_id)
                              from streams
                              where stream_date >= '2021-01-01' and stream_date <= '2021-12-31' ))
select count(account_id) as accounts_count
from temp1;