Pass value in current row into subquery within a conditional count function-CodePudding

I'm trying to use a conditional count(distinct) to count only the cases where a value (date) in a subquery matches the value of date in the current row of the main query. Here's the code I have so far:

SELECT
  date,
  count(distinct (case when image_id in (select image_id from Image_Table where date = ???) then user_id end)) as num_images
FROM
  User_Table

But I don't know how to pass the current value of "date" into the subquery that's inside the When clause.

The intended result would list each unique date that appears in User_Table and, for each date, it collects all rows that have an image_id value that also appears in Image_Table with the same date, and counts the distinct user_id values from those rows.

CodePudding user response：

select date, count(distinct user_id)
from User_Table u inner join Image_Table i
    on i.user_id = u.user_id and i.date = u.date
group by i.date;

This could be done with a subquery as well:

select u.date, count(distinct u.user_id)            
from User_Table u
where exists (
    select 1 from Image_Table i
    where i.user_id = u.user_id and i.date = u.date
)
group by u.date;