CREATE
OR REPLACE TABLE DEV_ENGINEERING.RBOMBERG_DBT.ORCHARD_DATA AS
select *
from (
         select GLOBAL_PARTICIPANT_ID, SPOTIFY_ARTIST_ID, SUBGENRE, COUNTRY_CODE, count(RELEASEID) as total_releases
         from (
                  select distinct tracks.RELEASEID,
                                  TRACKS.RELEASENAME,
                                  dtc.upc,
                                  -- dtc.track_unique_id,
                                  sg.name       as subgenre,
                                  g.GENRENAME,
                                  lp_pi_t.LABEL_PARTICIPANT_ID,
                                  gp_r_lp.GLOBAL_PARTICIPANT_ID,
                                  gp.SPOTIFY_ID as SPOTIFY_ARTIST_ID,
                                  gp.CHARTMETRIC_ID,
                                  gp.NAME,
                                  gp.COUNTRY_CODE
                  from (
                           select DR.RELEASEID, DR.RELEASENAME, DR.ARTISTID
                           from FACTS.PROD.DIM_RELEASE DR
                           where DR.FORMAT in ('Single', 'EP', 'Full Length')
                             and DR.RELEASEDATE > ADD_MONTHS(current_date(), -36)
                       ) tracks
                           inner join facts.prod.dim_track_clean_mv dtc on dtc.upc = tracks.releaseid
                           inner join facts.prod.release_subgenre rsubg on rsubg.UPC = TRACKS.releaseid
                           inner join facts.prod.DIM_SUBGENRE sg on sg.SUBGENREID = rsubg.SUBGENRE_ID
                           inner join facts.prod.DIM_GENRE G on G.genreid = sg.GENREID
                           INNER JOIN facts.prod.label_participant_participated_in_orchard_track lp_pi_t
                                      ON dtc.track_unique_id = lp_pi_t.track_id
                           INNER JOIN facts.prod.global_participant_represents_label_participant gp_r_lp
                                      ON lp_pi_t.label_participant_id = gp_r_lp.label_participant_id
                           LEFT JOIN facts.prod.global_participant_merged_to_global_participant gp_mt_gp
                                     ON gp_r_lp.global_participant_id = gp_mt_gp.global_participant_id_from
                           INNER JOIN facts.prod.global_participant gp
                                      ON COALESCE(gp_mt_gp.global_participant_id_to, gp_r_lp.global_participant_id) =
                                         gp.id
                  where lp_pi_t.participated_as IN ('remixer', 'performer', 'featuring')
                  order by tracks.releaseid
              ) data
         group by 1, 2, 3, 4
     ) data_