Clickhouse Array Join with Left Join and Computed Counts

Question

I have a SearchImpressions table that has a nested type column that holds arrays of Product_Id and Price that were shown during a search. I also have a table that holds the details on when a user clicks on one of the search results.

Question:

Given a product id I would like to find out the total impressions and the count of clicks grouped by day.

Product Search Results Table

EventDateTime Query Results(ProductId[], Price[])

Product Clicks Table

EventDateTime ProductId

Required Output

EventDate    ProductId TotalImpressions TotalClicks

11-11-2020   0001      56               6
12-11-2020   0002      21               0

I have attempted this but both counts appear the same

SELECT pr.EventDate,
       impressions.ProductId,
       count(impressions.ProductId) As TotalImpressions,
       count(clicks.productId) as TotalClicks
FROM ProductResults pr
ARRAY JOIN results as impressions
LEFT JOIN ProductClicks clicks on
          impressions.ProductId = clicks.ProductId
GROUP BY pr.EventDate,
         pr.DealershipId,
         pr.Vrm
ORDER BY pr.EventDate Desc;

Thanks

Answer 1

It looks like need to add predicate to count-aggregate function for clicks.productId or use uniqIf-function:

SELECT pr.EventDate,
       impressions.ProductId,
       count(impressions.ProductId) As TotalImpressions,
       countIf(clicks.productId != 0) as TotalClicks1 /* <-- v.1 */
       uniqIf(clicks.productId, clicks.productId != 0) as TotalClicks2 /* <-- v.2 */
..

Answer 2

 SELECT pr.EventDate,
        impressions.ProductId,
        count() As TotalImpressions,
        clicks.TotalClicks
 FROM ProductResults pr ARRAY JOIN results as impressions
 LEFT JOIN    (select ProductId, count(clicks.productId) TotalClicks 
               from  ProductClicks group by ProductId 
              ) clicks on impressions.ProductId = clicks.ProductId
 GROUP BY pr.EventDate, impressions.ProductId
 ORDER BY pr.EventDate Desc;