# View: market_share_test

**View Name:** market_share_test
**Table Source:** `Derived from: Max_Months, Monthly_Metrics, Prod.Fact_Market_Share`
**File Path:** `market_share_test.view.lkml`

## Overview

- **File Size:** 8711 bytes
- **Lines of Code:** 283
- **Dimensions:** 18
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
- ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>
- 72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}%</span>

## Dimensions

| Name | Type |
|------|------|
| `store` | string |
| `max_month` | date |
| `previous_month` | date |
| `max_month_rows` | number |
| `previous_month_rows` | number |
| `max_month_volume` | number |
| `previous_month_volume` | number |
| `total_store_volume` | number |
| `store_revenue_usd` | number |
| `orchard_gross_revenue_usd` | number |
| `prev_month_store_volume` | number |
| `prev_month_store_revenue` | number |
| `prev_month_orchard_revenue` | number |
| `store_volume_pct_change` | number |
| `store_revenue_pct_change` | number |
| `orchard_revenue_pct_change` | number |
| `row_pct_change` | number |
| `volume_pct_change` | number |

## Measures

| Name | Type |
|------|------|
| `count` | count |

## Derived Table

```sql
sql: WITH max_months AS (
      SELECT
      store,
      MAX(max_activity_month) AS max_month,
      DATEADD (MONTH, -1, MAX(max_activity_month)) AS previous_month
      FROM
      facts.prod.fact_market_share
      GROUP BY
      store
      ),
      monthly_metrics AS (
      SELECT
      f.store,
      f.max_activity_month,
      COUNT(*) AS total_rows,
      SUM(f.orchard_streams) AS total_volume,
      SUM(f.STORE_STREAMS) AS total_store_volume,
      SUM(f.STORE_REVENUE_USD) AS STORE_REVENUE_USD,
      SUM(f.ORCHARD_GROSS_REVENUE_USD) AS ORCHARD_GROSS_REVENUE_USD,
      LAG (COUNT(*)) OVER (
      PARTITION BY f.store
      ORDER BY
      f.max_activity_month
      ) AS prev_month_rows,
      LAG (SUM(f.orchard_streams)) OVER (
      PARTITION BY f.store
      ORDER BY
      f.max_activity_month
      ) AS prev_month_volume,
      LAG (SUM(f.STORE_STREAMS)) OVER (
      PARTITION BY f.store
      ORDER BY
      f.max_activity_month
      ) AS prev_month_store_volume,
      LAG (SUM(f.STORE_REVENUE_USD)) OVER (
      PARTITION BY f.store
      ORDER BY
      f.max_activity_month
      ) AS prev_month_store_revenue,
      LAG (SUM(f.ORCHARD_GROSS_REVENUE_USD)) OVER (
      PARTITION BY f.store
      ORDER BY
      f.max_activity_month
      ) AS prev_month_orchard_revenue
      FROM
      facts.prod.fact_market_share AS f
      WHERE
      f.max_activity_month IN (
      SELECT
      max_month
      FROM
      max_months
      UNION ALL
      SELECT
      previous_month
      FROM
      max_months
      )
      GROUP BY
      f.store,
      f.max_activity_month
      )
      SELECT
      m.store,
      m.max_month,
      m.previous_month,
      COALESCE(mv.total_rows, 0) AS max_month_rows,
      COALESCE(mv.prev_month_rows, 0) AS previous_month_rows,
      COALESCE(mv.total_volume, 0) AS max_month_volume,
      COALESCE(mv.prev_month_volume, 0) AS previous_month_volume,

      COALESCE(mv.total_store_volume,0) AS total_store_volume,
      COALESCE(mv.STORE_REVENUE_USD,0) AS STORE_REVENUE_USD,
      COALESCE(mv.ORCHARD_GROSS_REVENUE_USD,0) AS ORCHARD_GROSS_REVENUE_USD,

      COALESCE(mv.prev_month_store_volume,0) AS prev_month_store_volume,
      COALESCE(mv.prev_month_store_revenue,0) AS prev_month_store_revenue,
      COALESCE(mv.prev_month_orchard_revenue,0) AS prev_month_orchard_revenue,
      CASE
      WHEN COALESCE(mv.prev_month_rows, 0) = 0 THEN NULL
      ELSE (
      (
      COALESCE(mv.total_rows, 0) - COALESCE(mv.prev_month_rows, 0)
      ) / COALESCE(mv.prev_month_rows, 0)
      ) * 100
      END AS row_pct_change,
      CASE
      WHEN COALESCE(mv.prev_month_volume, 0) = 0 THEN NULL
      ELSE (
      (
      COALESCE(mv.total_volume, 0) - COALESCE(mv.prev_month_volume, 0)
      ) / COALESCE(mv.prev_month_volume, 0)
      ) * 100
      END AS volume_pct_change,
      CASE
    WHEN COALESCE(mv.prev_month_store_volume, 0) = 0 THEN NULL
    ELSE (
      (COALESCE(mv.total_store_volume, 0) - COALESCE(mv.prev_month_store_volume, 0))
      / COALESCE(mv.prev_month_store_volume, 0) * 100
    )
    END AS store_volume_pct_change,
    CASE
    WHEN COALESCE(mv.prev_month_store_revenue, 0) = 0 THEN NULL
    ELSE (
      (COALESCE(mv.STORE_REVENUE_USD, 0) - COALESCE(mv.prev_month_store_revenue, 0))
      / COALESCE(mv.prev_month_store_revenue, 0) * 100
    )
    END AS store_revenue_pct_change,
    CASE
    WHEN COALESCE(mv.prev_month_orchard_revenue, 0) = 0 THEN NULL
    ELSE (
      (COALESCE(mv.ORCHARD_GROSS_REVENUE_USD, 0) - COALESCE(mv.prev_month_orchard_revenue, 0))
      / COALESCE(mv.prev_month_orchard_revenue, 0) * 100
    )
    END AS orchard_revenue_pct_change
      FROM
      max_months AS m
      LEFT JOIN monthly_metrics AS mv ON m.store = mv.store
      AND m.max_month = mv.max_activity_month
      ORDER BY
      m.store ;;
```