# View: dt_fact_sales_market_share

**View Name:** dt_fact_sales_market_share
**Table Source:** `Derived from: Prod.Dim_Country, Prod.Fact_Market_Share, Prod.Fact_Sales`
**File Path:** `dt_fact_sales_market_share.view.lkml`

## Overview

- **File Size:** 4410 bytes
- **Lines of Code:** 162
- **Dimensions:** 4
- **Measures:** 8
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `country_code` | string |
| `storeid` | number |
| `labelid` | number |
| `is_adsup` | yesno |

## Measures

| Name | Type |
|------|------|
| `net_receipt` | sum |
| `gross` | sum |
| `units` | sum |
| `orchard_streams` | sum |
| `store_streams` | sum |
| `users` | sum |
| `orchard_gross_revenue_usd` | sum |
| `store_revenue_usd` | sum |

## Dimension Groups

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

## Derived Table

```sql
sql:
    SELECT
        DATE_FROM_PARTS(fs.activityyear, fs.activitymonth, 1) AS activity_month,
        fs.country_code,
        fs.storeid,
        fs.labelid,
        fs.is_adsup,
        fs.net_receipt,
        fs.gross,
        fs.units,
        ms.orchard_streams,
        ms.store_streams,
        ms.users,
        ms.orchard_gross_revenue_usd,
        ms.store_revenue_usd
    FROM (
        SELECT
            fs.activityyear,
            fs.activitymonth,
            dc.country_code,
            fs.storeid,
            fs.labelid,
            CASE
                WHEN fs.transactiontypeid IN (9,10,27,39) THEN TRUE
                WHEN fs.transactiontypeid IN (1,14,16,17,20,29,48) THEN FALSE
                END AS is_adsup,
            SUM(fs.net_receipt) AS net_receipt,
            SUM(fs.gross) AS gross,
            SUM(fs.sales) AS units
        FROM facts.prod.fact_sales fs
            LEFT JOIN facts.prod.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)
        GROUP BY 1,2,3,4,5,6
    ) fs
        INNER JOIN (
            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 facts.prod.fact_market_share
            GROUP BY 1,2,3,4,5
        ) ms ON fs.activityyear = ms.activityyear
            AND fs.activitymonth = ms.activitymonth
            AND fs.country_code = ms.country_code
            AND fs.storeid = ms.storeid
            AND fs.is_adsup = ms.is_adsup ;;
```