[Solved] Which users completed a list: How do I query this in as few steps as possible?

by


A good approach might be to use count(*) and GROUP BY.
So you get a count of the number of answer rows per user per list in the completed table then join to a subquery which gets you a total count of items in the list.

Something like this:

select user_id, c.list_id, count(user_id) as list_count, case when item_total-count(user_id)=0 then 'Complete' else 'Incomplete' end as list_status 
from completed c
left join (select list_id, count(*) as item_total from items group by list_id) aa on aa.list_id=c.list_id
group by user_id, c.list_id, item_total

For your “bonus points” part, you would need to give more detail about the structure of the table in order to determine a rule to exclude newer entries.

If you have some sort of timestamp or index, you could use a dense_rank to get the first item by date per user per list for example.

2

solved Which users completed a list: How do I query this in as few steps as possible?