SQL without using any union clauses

user_8127272 0
Viewed 31

I found this question on Glassdoor. Is there an efficient way of writing this query without using any union clause.

Given a table of interaction between users (user_a | user_b | curr_date), find number of users who had more than 5 interactions yesterday (assume there is only one unique interaction between a pair of users per day).

SELECT user_a, COUNT(*)
FROM users
WHERE curr_date = current_date-1
GROUP BY user_a
HAVING COUNT(*)>5

UNION

SELECT user_b, COUNT(*)
FROM users
WHERE curr_date = current_date-1
GROUP BY user_b
HAVING COUNT(*)>5;
postgresql sql
1 Answers

Presumably, the question arises be cause an interaction is stored only once, but counts for both users. You can use a lateral join to unpivot the data and then aggregate:

SELECT user_ab COUNT(*)
FROM users u CROSS JOIN LATERAL
     (VALUES (user_a), (user_b)) v(user_ab)
WHERE curr_date = current_date - INTERVAL '1 DAY'
GROUP BY user_ab
HAVING COUNT(*) > 5;
user_11440350
Related