I'm practicing for my upcoming SQL interview using questions from bigtechinterviews.com and am currently stuck on my solution (See my solution below).

Prompt: Write a query to find active users on the Free plan to contact them 1 day before their trial expires and notify them of the number of active servers they are using. Return the email, the number of active servers, and the date they should be contacted. (Hint: A free trial lasts 14 days.)

Table: users

user_id	email	signup	active	plan_id
1	[email protected]	current_timestamp::DATE-11	true	1
2	[email protected]	current_timestamp::DATE-21	false	2
3	[email protected]	current_timestamp::DATE-12	false	2
4	[email protected]	current_timestamp::DATE-3	true	3
5	[email protected]	current_timestamp::DATE-5	false	1
6	[email protected]	current_timestamp::DATE-8	true	2
7	[email protected]	current_timestamp::DATE-6	true	3
8	[email protected]	current_timestamp::DATE-12	true	1

Table: servers

user_id	server	active
1	67.55.37.78	true
5	37.61.109.175	false
3	158.66.86.52	false
2	194.17.5.72	false
7	25.132.11.177	true
8	159.235.115.252	false
6	40.23.107.96	true
6	168.195.126.210	true
5	184.202.205.222	false
1	34.171.164.42	true
3	38.231.102.133	false
6	189.44.114.58	true
4	56.200.112.239	true
1	134.214.244.247	false
4	253.146.50.201	true
5	77.181.43.159	false
1	242.71.73.107	true
2	64.175.228.245	false
5	124.80.68.144	false
8	239.90.220.90	true
6	74.182.172.182	true
6	146.180.155.33	true
3	7.1.210.72	false
5	240.126.4.87	false

Table: plans

plan_id	plan_type	supported_servers
1	free	1
2	paid	3
3	pro	5

Full question linked here: https://app.bigtechinterviews.com/challenge/2RE4RjguRPJxarKxtIUJDD

Attempted Solution:

SELECT
     u.email
    ,COUNT(s.server) servers
    ,signup::DATE   interval '13 days' target_day
FROM
    users u 
JOIN
    "plans" p
ON
    p.plan_id = u.plan_id
     AND p.plan_type = 'free'
LEFT JOIN
    servers s
ON
    s.user_id = u.user_id
WHERE
    u.active = true
GROUP BY 
    1,3;

the expected output is:

email	servers	target_day
[email protected]	3	2022-08-25T00:00:00.000Z
[email protected]	1	2022-08-24T00:00:00.000Z

current output is:

email	servers	target_day
[email protected]	2	2022-10-10T00:00:00.000Z
[email protected]	4	2022-10-11T00:00:00.000Z

CodePudding user response：

select   u.email
        ,count(*)         as servers
        ,max(u.signup 13) as target_day
from     users u join servers s using(user_id) join plans p using(plan_id)
where    s.active = true
and      u.active = true
and      p.plan_type = 'free'
group by u.email

email	servers	target_day
[email protected]	1	2022-10-11
[email protected]	3	2022-10-12

Fiddle

CodePudding user response：

SELECT u.email
     , COALESCE(s.servers, 0) AS servers
     , u.signup   13 AS target_day
FROM   users   u
LEFT   JOIN LATERAL (
   SELECT s.user_id, count(*) AS servers 
   FROM   servers s
   WHERE  s.user_id = u.user_id
   AND    s.active
   GROUP  BY 1
   ) s ON true
WHERE  u.active
AND    u.plan_id = (SELECT plan_id FROM plans WHERE plan_type = 'free')
AND    u.signup > CURRENT_DATE - 14;

fiddle

This avoids GROUP BY in the outer SELECT.

LEFT JOIN to keep users without active servers in the loop. Consequently, COALESCE in the outer SELECT list.

LATERAL because I assume only a very small percentage of all users qualify, so it's cheaper to aggregate servers only for the select few.

signup > CURRENT_DATE - 14 to exclude older rows, which must have been informed already. Maybe just removes one day, maybe more - depending on undisclosed information how those free plans are inactivated exactly.