create view production.aggregated_accounting_store_view as
SELECT storeid, storename AS store_name, 
          CASE 'storename' 
            WHEN 'Microsoft Zune Music'           THEN 'Xbox Music'
            WHEN 'Microsoft X-Box'                THEN 'Xbox Video'
            WHEN 'Cricket Communications, Inc.'   THEN 'Muve'
            WHEN 'Google Music'                   THEN 'Google'
            WHEN 'Amazon Digital Services Inc.'   THEN 'Amazon'
            WHEN 'Amazon DOD'                     THEN 'Amazon DiskOnDemand'
            WHEN 'Amazon Instant Video'           THEN 'Amazon Video'
            WHEN 'Livewire Mobile, Inc.'          THEN 'Livewire'
            WHEN 'Moderati Inc.'                  THEN 'Moderati'
            WHEN 'REAL Networks'                  THEN 'Real Netw'
            WHEN 'Beatport Digital Download Network' THEN 'Beatport'
            WHEN 'BELL MOBILITY INC'              THEN 'Bell Mobility'
            WHEN 'Oasys Mobile'                   THEN 'Oasys'
            WHEN 'Dada S.p.A.'                    THEN 'Dada'
            WHEN 'Yandex LLC'                     THEN 'Yandex'
            WHEN 'Sony Ericsson Mobile AB'        THEN 'Sony Ericsson'
            WHEN 'Sony Network Entertainment'     THEN 'Sony NE'
            WHEN 'Leapfrog Enterprises, Inc.'     THEN 'Leapfrog'
            WHEN 'Basepoint Media'                THEN 'Basepoint'
            WHEN 'Snocap, Inc.'                   THEN 'Snocap'
           ELSE 'storename'
          END AS store_name_short
          ,

          CASE 'storename' 
            WHEN 'YouTube'                        THEN 'YouTube'
            WHEN 'YouTube Web Videos'             THEN 'YouTube'
            WHEN 'YouTube Movies'                 THEN 'YouTube'
            WHEN 'Microsoft Zune Music'           THEN 'Xbox'
            WHEN 'Microsoft X-Box'                THEN 'Xbox'
            WHEN 'Spotify'                        THEN 'Spotify'
            WHEN 'Rhapsody'                       THEN 'Rhapsody'
            WHEN 'rDio'                           THEN 'rDio'
            WHEN 'Cricket Communications, Inc.'   THEN 'Muve'
            WHEN 'iTunes'                         THEN 'iTunes'
            WHEN 'Google Music'                   THEN 'Google'
            WHEN 'eMusic'                         THEN 'eMusic'
            WHEN 'deezer'                         THEN 'deezer'
            WHEN 'Amazon Digital Services Inc.'   THEN 'Amazon'
            WHEN 'Amazon Instant Video'           THEN 'Amazon'
            WHEN 'Verizon'                        THEN 'Verizon'
            WHEN 'Napster'                        THEN 'Rhapsody'
            WHEN 'MediaNet'                       THEN 'MediaNet'
            WHEN 'VEVO'                           THEN 'Vevo'
            WHEN 'Netflix'                        THEN 'Netflix'
            WHEN 'Hulu'                           THEN 'Hulu'
            WHEN 'Sony Ericsson Mobile AB'        THEN 'Sony'
            WHEN 'Sony Connect'                   THEN 'Sony'
            WHEN 'Sony Network Entertainment'     THEN 'Sony'
           ELSE 'zUncategorized Store'
          END AS store_group
          ,

          CASE 'storename' 
            WHEN 'YouTube'                        THEN 'YouTube'
            WHEN 'YouTube Web Videos'             THEN 'YouTube'
            WHEN 'YouTube Movies'                 THEN 'YouTube'
            WHEN 'Microsoft Zune Music'           THEN 'Xbox'
            WHEN 'Microsoft X-Box'                THEN 'Xbox'
            WHEN 'Spotify'                        THEN 'Spotify'
            WHEN 'Rhapsody'                       THEN 'Rhapsody'
            WHEN 'rDio'                           THEN 'rDio'
            WHEN 'Cricket Communications, Inc.'   THEN 'Muve'
            WHEN 'iTunes'                         THEN 'iTunes'
            WHEN 'Google Music'                   THEN 'Google'
            WHEN 'eMusic'                         THEN 'eMusic'
            WHEN 'deezer'                         THEN 'deezer'
            WHEN 'Amazon Digital Services Inc.'   THEN 'Amazon'
            WHEN 'Amazon Instant Video'           THEN 'Amazon'
            WHEN 'Napster'                        THEN 'Rhapsody'
           ELSE 'zUncategorized Store'
          END AS store_group_less
          ,

          CASE 'storename' 
            WHEN 'Spotify'                        THEN 'Streaming'
            WHEN 'Cricket Communications, Inc.'   THEN 'Streaming'
            WHEN 'iTunes'                         THEN 'Download'
            WHEN 'deezer'                         THEN 'Streaming'
            WHEN 'Amazon Digital Services Inc.'   THEN 'Download'
            WHEN 'Amazon Instant Video'           THEN 'Download'
           ELSE 'zUncategorized Store'
          END AS store_musicbucket
          ,

          CASE 'storename' 
            WHEN 'YouTube'                        THEN 'Video Services'      
            WHEN 'YouTube Web Videos'             THEN 'Video Services'                 
            WHEN 'YouTube Movies'                 THEN 'Video Services'             
            WHEN 'VEVO'                           THEN 'Video Services'   
            WHEN 'iTunes'                         THEN 'Retail Video'
           ELSE 'zUncategorized Store'
          END AS store_videobucket

          , false as is_store_itunesUSA
    FROM production.dim_store ;