SQL - Snowflake - Inner Join not working as expected

user_12821675 0
Viewed 598

I have a table ADS in snowflake like so (data is being inserted each day), note there are duplicates entries on rows 3 and 4:

ID REPORT_DATE CLICKS IMPRESSIONS
1 Jan 01 20 400
1 Jan 02 25 600
1 Jan 03 80 900
1 Jan 03 80 900
2 Jan 01 30 500
2 Jan 02 55 650
2 Jan 03 90 950

I want to select all entries based on ID with the max REPORT_DATE - essentially I want to know the latest number of CLICKS and IMPRESSIONS for each ID:

ID REPORT_DATE CLICKS IMPRESSIONS
1 Jan 03 80 900
2 Jan 03 90 950

This query successfully gives me the max DATE for each ID:

SELECT
  MAX(REPORT_DATE),
  ID
FROM ADS
GROUP BY 
  ID;

Result:

ID MAX(REPORT_DATE)
1 Jan 03
2 Jan 03

However, when I try to conduct an inner join, duplicates arise:

SELECT 
  a.ID,
  a.REPORT_DATE,
  a.CLICKS,
  a.IMPRESSIONS
FROM ADS a
INNER JOIN (
  SELECT
    MAX(REPORT_DATE),
    ID
  FROM ADS
  GROUP BY 
      ID
) b 
ON a.ID = b.ID
AND a.REPORT_DATE = b.REPORT_DATE;

Result:

ID REPORT_DATE CLICKS IMPRESSIONS
1 Jan 03 80 900
1 Jan 03 80 900
2 Jan 03 90 950

How can I construct my query to remove these duplicates?

snowflake-cloud-data-platform inner-join sql
2 Answers

You could use QUALIFY and ROW_NUMBER():

SELECT a.ID,a.REPORT_DATE,a.CLICKS,a.IMPRESSIONS
FROM ADS a
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REPORT_DATE DESC) = 1;

Please note that ORDER BY REPORT_DATE is not stable(in case of a tie). I would suggest adding another column for sorting that is the tuple is always unique.

If the rows that have a tie are the same it actually is not an issue.

user_50708790

You can use row_number() window function:

select id, report_date, clicks, impresions from
(
 select id, report_date, clicks, impresions, row_number()over(partition by id order 
 by report_date desc) rnk from ADs
)t
where rn=1
user_86516010
Related