CODEWITHSUNDEEP
clickhouse
redsquare
redsquare

Reputation: 78667

Clickhouse Array Join with Left Join and Computed Counts

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

Upvotes: 0

Views: 1686

Answers (2)

vladimir
vladimir

Reputation: 15218

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 */
..

Upvotes: 1

Denny Crane
Denny Crane

Reputation: 13300

 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;

Upvotes: 1

Related Questions