Postgres query to filter by distinct column value AND further by distinct week

Ive got a query which will return me the count of distinct ‘artists’ on a playlist, but id like to further filter this by unique weeks using the created_at timestamp column

my current query is:

select count(track), track,
date_part('year', created_at::date) as year,
date_part('week', created_at::date) as week
from public."Playlist"
group by track,year, week;

which returns me the distinct tracks and count of distinct tracks and the week created_at was in. Im having trouble constructing the query to now filter this further by unique weeks.

My aim is to end up with a list of tracks and count of the distinct WEEKS that they were released in

Thanks

HM

Answer

My aim is to end up with a list of tracks and count of the distinct WEEKS that they were released in.

I think you want:

select 
    track,
    count(distinct date_trunc('week', created_at)) no_distinct_weeks
from public."Playlist"
group by track