SQLite查询：如何筛选用户数超5的应用的唯一登录记录？

To get only the unique user records for apps that had more than 5 distinct users log in during the month, you can use a subquery or CTE to first identify qualifying apps, then filter your original dataset to include only those apps. Here are two straightforward approaches:

Using a CTE (Readable, Maintainable Version) #

WITH qualifying_apps AS (
    SELECT AppName
    FROM "macanta_user_track_records"
    WHERE "LoginDate" >= '2018-04-01' AND "LoginDate" <= '2018-04-30'
    GROUP BY AppName
    HAVING COUNT(DISTINCT Id) > 5
)
SELECT DISTINCT t.Id, t.AppName
FROM "macanta_user_track_records" t
JOIN qualifying_apps q ON t.AppName = q.AppName
WHERE t."LoginDate" >= '2018-04-01' AND t."LoginDate" <= '2018-04-30'
ORDER BY t.AppName;

Using a Subquery in the WHERE Clause (Compact Version) #

If you prefer a more concise query without CTEs:

SELECT DISTINCT Id, AppName
FROM "macanta_user_track_records"
WHERE "LoginDate" >= '2018-04-01' AND "LoginDate" <= '2018-04-30'
AND AppName IN (
    SELECT AppName
    FROM "macanta_user_track_records"
    WHERE "LoginDate" >= '2018-04-01' AND "LoginDate" <= '2018-04-30'
    GROUP BY AppName
    HAVING COUNT(DISTINCT Id) > 5
)
ORDER BY AppName;

How This Works #

1. Identify Qualifying Apps: The inner query (or CTE) groups records by AppName, counts the number of unique user IDs per app, and keeps only apps where this count exceeds 5.
2. Filter Original Records: We use this list of apps to narrow down the original dataset, retaining only distinct user-app pairs for those qualifying apps.
3. Preserve Original Sorting: The ORDER BY AppName clause maintains the same sorting as your original query.

This will return exactly the subset you want—only the user records for apps that had 6 or more unique users log in during April 2018.

内容的提问来源于stack exchange，提问作者Peter Daly-Dickson