CODEWITHSUNDEEP
sqlgoogle-bigquery
D_usv
D_usv

Reputation: 433

Count of id per day using window function

I'm trying to count track_uri that are associated to a given playlist_uri in a day in a one month window and have composed the following sql:

SELECT
  playlist_uri, playlist_date, track_uri, count(track_uri) 
  over (partition by playlist_uri, playlist_date) as count_tracks
FROM
  tbl1
WHERE
  _PARTITIONTIME BETWEEN '2017-09-09' AND '2017-10-09'
  AND playlist_uri in (
    SELECT playlist_uri from tbl2 WHERE playlist_owner  = "spotify"
  )

However I am getting the following output:

enter image description here

I instead would like it to show me the count of track_uri for each playlist_uri on each day.

Would really appreciate some help with this.

Upvotes: 0

Views: 93

Answers (1)

Nhan Nguyen
Nhan Nguyen

Reputation: 410

Not sure if I understand your question correctly, but if you might not need to use the window function for that:

SELECT
  playlist_uri, playlist_date, COUNT(DISTINCT track_uri)
FROM
  tbl1
WHERE
  _PARTITIONTIME BETWEEN '2017-09-09' AND '2017-10-09'
  AND playlist_uri in (
    SELECT playlist_uri from tbl2 WHERE playlist_owner  = "spotify"
  )
GROUP BY 1, 2;

Upvotes: 1

Related Questions