Explain SQL "SELECT count(*) FROM t AS 2 WHERE t1.col1 = t2.col1"-CodePudding

Can anyone, please, explain syntax of following request? The question is - if test2 is a result of count function, it is just a number. How can it be treated as a table (revenue.country_code = test2.country_code)? This code works, but I don't understand how. Thanks a lot for any answer.

SELECT customer_user_id, revenue, country_code FROM revenue 
WHERE
(SELECT count(*) 
FROM revenue AS test2 
WHERE revenue.country_code = test2.country_code 
AND test2.revenue > revenue.revenue) < 5 
and media_source = 'facebook'
ORDER BY country_code, revenue DESC;

CodePudding user response：

This is called a Correlated Subquery. The Subquery contains a reference to the table in the main query in its WHERE clause and it works similar to a join.

What this is saying in english is "We compare the number of records in this table for this country_id that have a higher revenue than this record's revenue. If that count is less than 5, then keep this record".

If it helps to understand, this could also be written with window functions:

SELECT *
FROM 
   (
       SELECT customer_user_id, revenue, country_code 
           ,DENSE_RANK() OVER (PARTITION BY country_code ORDER BY revenue DESC) as revenuerank
       FROM revenue
       WHERE media_source = 'facebook'
   ) sub
WHERE sub.revenuerank < 5