# View: dt_fact_sales_market_share_brazil

**View Name:** dt_fact_sales_market_share_brazil
**Table Source:** `Derived from: Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Base_Dim_Country, Dbt_Prod.Base_Fact_Market_Share, Dbt_Prod.Base_Fact_Sales, Intelligence.Dbt_Prod + 3 more`
**File Path:** `dt_fact_sales_market_share_brazil.view.lkml`

## Overview

- **File Size:** 6423 bytes
- **Lines of Code:** 190
- **Dimensions:** 3
- **Measures:** 5
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- ,##0.0,,\" M\"; #,###"
- ,##0.0,,\" M\"; #,###"

## Dimensions

| Name | Type |
|------|------|
| `storename` | string |
| `orchard_stream_fsm_store_streams__100` | number |
| `orchard_stream_fs__o_unitsm_store_streams__100` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |
| `store_streams` | sum |
| `units` | sum |
| `orchard_streams` | sum |
| `orchard_stream_fs` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `activity_month` | time |

## SQL Comments

- Find label level details
- find Market Share level detials
- find store level streams from fact sales
- o.country_code,
- o.storeid,

## Derived Table

```sql
sql: -- Find label level details

      with orchard_label_date AS (
                  SELECT
                  fs.activityyear,
                  fs.activitymonth,
                  dc.country_code,
                  fs.storeid,
                  fs.labelid,
                  CASE
                      WHEN fs.transactiontypeid IN (9,10,27,39,107) THEN TRUE
                      WHEN fs.transactiontypeid IN (1,14,16,17,20,29,48,101,108,109) THEN FALSE
                      END AS is_adsup,
                  SUM(fs.net_receipt) AS net_receipt,
                  SUM(fs.gross) AS gross,
                  SUM(fs.sales) AS units
              FROM intelligence.dbt_prod.base_fact_sales fs
                  LEFT JOIN intelligence.dbt_prod.base_dim_country dc ON fs.countryid = dc.countryid
              WHERE storeid IN (1,187,286,348,496,569,716)
                  AND transactiontypeid IN (9,10,27,39,1,14,16,17,20,29,48,101,107,108,109)
              GROUP BY 1,2,3,4,5,6
      ),

      -- find Market Share level detials

      market_share AS (
      SELECT
                      YEAR(activity_month544) AS activityyear,
                      MONTH(activity_month544) AS activitymonth,
                      country_code,
                      CASE store
                          WHEN 'Amazon Prime' THEN 187
                          WHEN 'Amazon Unlimited' THEN 716
                          WHEN 'Apple Music' THEN 1
                          WHEN 'Deezer' THEN 348
                          WHEN 'Google Play' THEN 496
                          WHEN 'Spotify' THEN 286
                          WHEN 'Youtube Red' THEN 569
                          END AS storeid,
                      is_adsup,
                      SUM(orchard_streams) AS orchard_streams,
                      SUM(store_streams) AS store_streams,
                      SUM(users) AS users,
                      SUM(orchard_gross_revenue_usd) AS orchard_gross_revenue_usd,
                      SUM(store_revenue_usd) AS store_revenue_usd
                  FROM intelligence.dbt_prod.base_fact_market_share
                  GROUP BY 1,2,3,4,5

      ),

      -- find store level streams from fact sales

      store_streams AS (

          SELECT
                  fs.activityyear,
                  fs.activitymonth,
                  dc.country_code,
                  fs.storeid,
                  facts_prod_dim_store.storename,
                  CASE
                      WHEN fs.transactiontypeid IN (9,10,27,39,107) THEN TRUE
                      WHEN fs.transactiontypeid IN (1,14,16,17,20,29,48,101,108,109) THEN FALSE
                      END AS is_adsup,
                  int_dbt_prod_awal_tier_groups.brand_name,
                  SUM(fs.sales) AS units
              FROM intelligence.dbt_prod.base_fact_sales fs
                  LEFT JOIN intelligence.dbt_prod.base_dim_country dc ON fs.countryid = dc.countryid
                  LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups ON fs.labelid = int_dbt_prod_awal_tier_groups.labelid
                  LEFT JOIN facts.prod.dim_store  AS facts_prod_dim_store ON fs.storeid = facts_prod_dim_store.storeid
              WHERE
                  fs.storeid IN (1,187,286,348,496,569,716)
                  AND fs.transactiontypeid IN (9,10,27,39,1,14,16,17,20,29,48,101,107,108,109)
                  AND dc.country_code = 'BR'
                  AND int_dbt_prod_awal_tier_groups.brand_name != 'altafonte'
              GROUP BY all
      )

      SELECT
          DATE_FROM_PARTS(o.activityyear, o.activitymonth, 1) AS activity_month,
          --o.country_code,
          --o.storeid,
          ss.storename,
          --o.labelid,
          --o.is_adsup,
          --o.net_receipt,
          --o.gross,
          m.store_streams,
          o.units,
          m.orchard_streams,
          sum(ss.units) as orchard_stream_fs,
          (orchard_stream_fs/m.store_streams) * 100 ,
          ((orchard_stream_fs - o.units)/m.store_streams) * 100,
          --m.users,
          --m.orchard_gross_revenue_usd,
         -- m.store_revenue_usd
          --ss.brand_name,
      FROM orchard_label_date o
      INNER JOIN market_share m
          ON o.activityyear = m.activityyear
         AND o.activitymonth = m.activitymonth
         AND o.country_code = m.country_code
         AND o.storeid = m.storeid
         AND o.is_adsup = m.is_adsup
      LEFT JOIN store_streams ss
         ON o.activityyear = ss.activityyear
         AND o.activitymonth = ss.activitymonth
         AND o.country_code = ss.country_code
         AND o.storeid = ss.storeid
         AND o.is_adsup = ss.is_adsup
         where activity_month > '2024-10-01' and o.labelid = '21786' and o.country_code = 'BR'
         --and ss.storename = 'Spotify'
         group by all
         order by 1 desc ;;
```