-
Notifications
You must be signed in to change notification settings - Fork 8
/
2020. Number of Accounts That Did Not Stream.py
95 lines (69 loc) · 2.31 KB
/
2020. Number of Accounts That Did Not Stream.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
2020. Number of Accounts That Did Not Stream
Medium
14
130
Add to List
Share
SQL Schema
Table: Subscriptions
+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id | int |
| start_date | date |
| end_date | date |
+-------------+------+
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.
Table: Streams
+-------------+------+
| Column Name | Type |
+-------------+------+
| session_id | int |
| account_id | int |
| stream_date | date |
+-------------+------+
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.
The query result format is in the following example.
Example 1:
Input:
Subscriptions table:
+------------+------------+------------+
| account_id | start_date | end_date |
+------------+------------+------------+
| 9 | 2020-02-18 | 2021-10-30 |
| 3 | 2021-09-21 | 2021-11-13 |
| 11 | 2020-02-28 | 2020-08-18 |
| 13 | 2021-04-20 | 2021-09-22 |
| 4 | 2020-10-26 | 2021-05-08 |
| 5 | 2020-09-11 | 2021-01-17 |
+------------+------------+------------+
Streams table:
+------------+------------+-------------+
| session_id | account_id | stream_date |
+------------+------------+-------------+
| 14 | 9 | 2020-05-16 |
| 16 | 3 | 2021-10-27 |
| 18 | 11 | 2020-04-29 |
| 17 | 13 | 2021-08-08 |
| 19 | 4 | 2020-12-31 |
| 13 | 5 | 2021-01-05 |
+------------+------------+-------------+
Output:
+----------------+
| accounts_count |
+----------------+
| 2 |
+----------------+
Explanation: Users 4 and 9 did not stream in 2021.
User 11 did not subscribe in 2021.
/* Write your T-SQL query statement below */
select count(*) as accounts_count from Subscriptions s
left join Streams t on
s.account_id = t.account_id
where (year(start_date) <= 2021 and year(end_date) >= 2021)
and year(stream_date) != 2021