- REFRESH aggregation_input;
- REFRESH FA_SS_SONGCONSUMPTION_CHART;
- REFRESH lu_week;
- REFRESH WEEKLYSONGCONSUMPTIONCHART;
- INSERT OVERWRITE ETL_WRK_CONSUMPTIONCHARTS_COLUMN (REPORTKEY,PEAKRANK,UNIFIEDSONGID,WEEKSONCHART,LWRANK,LW2RANK,WEEKID,DatasetKey)
- SELECT reportkey,CAST(MIN(thiswksrank) AS INT) PeakRank,uid AS UnifiedSondID,CAST(COUNT(*) AS INT) Weeksonchart
- ,CAST(MAX(CASE WHEN f.weekid = ai.previousweekid THEN thiswksrank ELSE NULL END) AS INT) lwrank
- ,CAST(MAX(CASE WHEN f.weekid = ai.prev2weekid THEN thiswksrank ELSE NULL END) AS INT) lw2rank
- ,ai.weekid,f.Datasetkey
- FROM FA_SS_SONGCONSUMPTION_CHART f
- ,(SELECT ai.weekid ,ai.previousweekid ,lw.previousweekid prev2weekid FROM aggregation_input ai JOIN lu_week lw ON ai.previousweekid = lw.weekid
- WHERE ai.aggregation_type = 'FireballCharts' limit 1 ) ai
- WHERE f.weekid < ai.weekid GROUP BY reportkey,uid,ai.weekid,f.Datasetkey;
- REFRESH ETL_WRK_CONSUMPTIONCHARTS_COLUMN;
- INSERT OVERWRITE FA_SS_SONGCONSUMPTION_CHART
- (id,reportkey, thiswksrank ,lastwkrank ,2weekrank ,peakposition ,weeksonchart ,artist ,Title ,Label ,Genre,ReleaseDate ,TWTotalVolume ,ChangeTotalVolume ,LWTotalVolume ,thiswkssales ,ChangeSongSales ,TWOnDemandAudioStreams ,ChangeAudioODStreams ,uid ,weekid , DatasetKey,Country,ATDTotalVolume,ATDSongSales,ATDOnDemandAudioStreams)
- PARTITION(weekid_bkt=202007)
- SELECT
- CAST(CONCAT (CAST(2532 AS string),CAST(sc.weekid AS string),CAST(row_number() OVER (ORDER BY sc.weekid,sc.twrank) + (2532 * 10) AS string)) AS BIGINT) AS id
- ,2532 AS reportkey
- ,sc.twrank AS thiswksrank
- ,ISNULL(sc.lwrank,cc.lwrank) AS lastwkrank
- ,cc.lw2rank AS 2weekrank
- ,CAST(CASE WHEN ISNULL(cc.peakrank, 9999) < sc.twrank THEN cc.peakrank ELSE sc.twrank END AS INT) AS peakposition
- ,CAST(ISNULL(cc.WeeksOnChart, 0) + 1 AS INT) AS WeeksOnChart
- ,sc.Artist
- ,sc.Title
- ,ISNULL(sc.Label, '') AS Label
- ,sc.CoreGenre AS Genre
- ,sc.releasedate AS ReleaseDate
- ,twtotal AS TWTotalVolume
- ,CASE WHEN CAST((((ISNULL(CAST(twtotal AS FLOAT), 0) - ISNULL(CAST(lwtotal AS FLOAT), 0)) / nullif(CAST(lwtotal AS FLOAT), 0)) * 100) AS INT) IS NULL THEN '' ELSE CAST(CAST((((ISNULL(CAST(twtotal AS FLOAT), 0) - ISNULL(CAST(lwtotal AS FLOAT), 0)) / nullif(CAST(lwtotal AS FLOAT), 0)) * 100) AS DECIMAL(12, 1)) AS string) END AS ChangeTotalVolume
- ,lwtotal AS LWTotalVolume
- ,TWSongsales
- ,CASE WHEN CAST((((ISNULL(CAST(TWSongsales AS FLOAT), 0) - ISNULL(CAST(LWSongSales AS FLOAT), 0)) / nullif(CAST(LWSongSales AS FLOAT), 0)) * 100) AS INT) IS NULL THEN '' ELSE CAST(CAST((((ISNULL(CAST(TWSongsales AS FLOAT), 0) - ISNULL(CAST(LWSongSales AS FLOAT), 0)) / nullif(CAST(LWSongSales AS FLOAT), 0)) * 100) AS DECIMAL(12, 1)) AS string) END AS ChangeSongSales
- ,twaudiostreams AS TWOnDemandAudioStreams
- ,CASE WHEN CAST((((ISNULL(CAST(twaudiostreams AS FLOAT), 0) - ISNULL(CAST(LWOnDemandAudioStreams AS FLOAT), 0)) / nullif(CAST(LWOnDemandAudioStreams AS FLOAT), 0)) * 100) AS INT) IS NULL THEN '' ELSE CAST(CAST((((ISNULL(CAST(twaudiostreams AS FLOAT), 0) - ISNULL(CAST(LWOnDemandAudioStreams AS FLOAT), 0)) / nullif(CAST(LWOnDemandAudioStreams AS FLOAT), 0)) * 100) AS DECIMAL(12, 1)) AS string) END AS ChangeAudioODStreams
- ,x.unifiedsongid
- ,sc.WeekID
- ,CAST(ds.datasetid AS INT) DatasetKey
- ,LOWER(sc.CountryCode)
- ,0 ATDTotalVolume
- ,0 ATDSongSales
- ,0 ATDOnDemandAudioStreams
- FROM WEEKLYSONGCONSUMPTIONCHART sc inner join song x on x.songkey = sc.songid
- JOIN di_dataset ds ON sc.CountryCode = ds.country
- LEFT JOIN ETL_WRK_CONSUMPTIONCHARTS_COLUMN cc ON cc.unifiedsongid = x.unifiedsongid AND ds.datasetkey=cc.datasetkey AND sc.weekid = cc.weekid
- LEFT JOIN (SELECT UID, thiswkssales AS LWSongSales, TWOnDemandAudioStreams AS LWOnDemandAudioStreams,ReportKey,DatasetKey FROM FA_SS_SONGCONSUMPTION_CHART c JOIN lu_week w ON w.previousweekid = c.WeekID AND w.weekid = 202007) lsc ON sc.SongID = lsc.UID AND CAST(ds.datasetid AS INT) = lsc.DatasetKey WHERE sc.WeekID =202007;
- REFRESH FA_SS_SONGCONSUMPTION_CHART;
[text] nambikai
Viewer
*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.
Editor
You can edit this paste and save as new: