QQ <- "

  WITH cnt AS 
      (
        SELECT countryid, countryname AS country, country_code, continent,
               CASE continent
                  WHEN 'North America' THEN 'N.America'
                  WHEN 'Europe' THEN 'Europe'
                  ELSE 'ROW'
                END AS continent_group
        FROM production.dim_country
      )
      SELECT
            artist,        fa.artistid AS artistid,     
            genre,
            label,             
            label_owner, label_is_accounting_distributor,
            label_country,  
            mkt_priority,       
            releasedate,   releaseid,                   releasename,                    sale_start_date,                  
            units,         upc
      FROM 
          (
              SELECT  fa.labelid, fa.genreid, fa.releaseid as upc, fa.artistid, fa.transactiontypeid, fa.countryid, 
                      fa.storeid,
                      units
              FROM 
                  (
                      SELECT 
                      labelid, storeid, genreid, releaseid, artistid, transactiontypeid, countryid,
                      sum(royaltydollar) AS royaltydollar, 
                      sum(units) AS units
                      FROM production.fact_analytics 
                      WHERE countryid = 1  
                        AND storeid = 1

                      GROUP BY labelid, genreid, releaseid, artistid, transactiontypeid, countryid, storeid
                  ) fa 

          ) FA
      LEFT JOIN 
          (
              SELECT releaseid, releasename, releasedate, sale_start_date, mkt_priority
              FROM production.dim_release
          ) Re
      ON Re.releaseid = FA.upc
      LEFT JOIN 
          (
              SELECT labelid, labelname AS label,
                     ownerid as label_ownerid, owner AS label_owner,
                     (ownerid in (113) OR labelid in (18655, 22285, 18805, 20365, 10234, 6765)) AS label_is_accounting_distributor,
                     c_l.country AS label_country, c_l.country_code AS label_country_code, 
                     c_l.continent AS label_continent, c_l.continent_group AS label_continent_group
              FROM production.dim_label l
              LEFT JOIN cnt c_l
              ON l.countryid = c_l.countryid
          ) La
      ON La.labelid = FA.labelid
      LEFT JOIN 
          (
              SELECT artistid, artistname AS artist
              FROM production.dim_artist a
          ) Ar
      ON Ar.artistid = FA.artistid
      LEFT JOIN 
          (
              SELECT genreid, genrename AS genre
              FROM production.dim_genre
          ) Ge
      ON Ge.genreid = FA.genreid

      ORDER BY units DESC
 "


QRES2 <- runQry(QQ)

write.csv(QRES2[!(label_is_accounting_distributor)][order(units, decreasing=TRUE)], file="USA Top iTunes Albums, All Time.csv")

.cb()

.o(getwd())