Francisco Cervera Francisco Cervera - 4 months ago 6
SQL Question

Clean up SQL query with nested tables

SELECT COUNT(*)
FROM (
SELECT l.userid
FROM (
SELECT DISTINCT userid, CAST(tmstmp AS DATE)
FROM logins
WHERE type <> 'mobile'
AND tmstmp BETWEEN '2013-04-21' AND '2014-04-21') l
GROUP BY l.userid
HAVING count(*) > 5) t;


Hello, Can I please have suggestions on how I can clean up this query... maybe a clever way to remove one of the nested tables? Thanks in advance.

Answer

It sounds like you are looking for an aggregate within another aggregate. I think this is your best option using a single subquery:

SELECT COUNT(*)
FROM (
    SELECT userid
    FROM logins
    WHERE type <> 'mobile' AND tmstmp BETWEEN '2013-04-21' AND '2014-04-21'
    GROUP BY userid
    HAVING count(distinct CAST(tmstmp AS DATE)) > 5
) t