Skip to content

Instantly share code, notes, and snippets.

@ByoungInKim

ByoungInKim/extract_recent_data_with_deduplicate_date.sql

Last active February 22, 2017 17:37
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ByoungInKim/814e11b09b05e6340f3e11cd20cdd780 to your computer and use it in GitHub Desktop.
Save ByoungInKim/814e11b09b05e6340f3e11cd20cdd780 to your computer and use it in GitHub Desktop.
Download ZIP
deduplicate data and then select recent data
Raw
extract_recent_data_with_deduplicate_date.sql
-- deduplicate with recent data
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY date_key,CountryName ORDER BY regdatetime DESC) AS ROWNUM, *
FROM temp where packageName = 'com.test'
) A
WHERE ROWNUM = 1 AND date_key > '2017-01-10' order by date_key asc
-- example
/*
data
|user_id|data|reg_datetime|
| 1 | 2 |2016-10-21 |
| 1 | 5 |2016-10-22 |
| 1 | 23 |2016-10-23 |
after qurey
|user_id|data|reg_datetime|
| 1 | 23 |2016-10-23 |
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment