Question 114296 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How do I solve this SQL interview question?

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
Tags:

3

Answers


  1. 0

    We need to get all the active servers for all the active users that are on a free plan. In your solution you counted inactive servers as well.

    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

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
  3. 0

    your solution includes all servers, not only the active ones. Add a condition to your left join on servers same style as you already have done with the join on plans:

    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 and s.active = true <---- only active ones
WHERE
    u.active = true
GROUP BY 
    1,3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search