# View: market_share_fact_sales_test

**View Name:** market_share_fact_sales_test
**Table Source:** `Derived from: Dbt_Prod.Dt_Fact_Sales_Marketshare, Intelligence.Dbt_Prod, Max_Months, Metrics_Comparison, Store_Months`
**File Path:** `market_share_fact_sales_test.view.lkml`

## Overview

- **File Size:** 8421 bytes
- **Lines of Code:** 280
- **Dimensions:** 21
- **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 |
|------|------|
| `storeid` | number |
| `current_month` | date |
| `current_rows` | number |
| `current_orchard_streams` | number |
| `current_store_streams` | number |
| `current_gross` | number |
| `current_units` | number |
| `current_store_revenue_usd` | number |
| `previous_month` | date |
| `previous_rows` | number |
| `previous_orchard_streams` | number |
| `previous_store_streams` | number |
| `previous_gross` | number |
| `previous_units` | number |
| `previous_store_revenue_usd` | number |
| `rows_pct_change` | number |
| `orchard_streams_pct_change` | number |
| `store_streams_pct_change` | number |
| `gross_pct_change` | number |
| `units_pct_change` | number |
| `revenue_pct_change` | number |

## Measures

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

## Derived Table

```sql
sql: WITH store_months AS (
      SELECT
      storeid,
      activity_month,
      COUNT(*) AS total_rows,
      SUM(orchard_streams) AS total_orchard_streams,
      SUM(store_streams) AS total_store_streams,
      SUM(gross) AS total_gross,
      SUM(units) AS total_units,
      SUM(store_revenue_usd) AS total_store_revenue_usd
      FROM
      intelligence.dbt_prod.dt_fact_sales_marketshare
      GROUP BY
      storeid,
      activity_month
      ),
      max_months AS (
      SELECT
      storeid,
      MAX(activity_month) AS max_month
      FROM
      store_months
      GROUP BY
      storeid
      ),
      metrics_comparison AS (
      SELECT
      curr.storeid,
      curr.activity_month AS current_month,
      curr.total_rows AS current_rows,
      curr.total_orchard_streams AS current_orchard_streams,
      curr.total_store_streams AS current_store_streams,
      curr.total_gross AS current_gross,
      curr.total_units AS current_units,
      curr.total_store_revenue_usd AS current_store_revenue_usd,
      prev.activity_month AS previous_month,
      prev.total_rows AS previous_rows,
      prev.total_orchard_streams AS previous_orchard_streams,
      prev.total_store_streams AS previous_store_streams,
      prev.total_gross AS previous_gross,
      prev.total_units AS previous_units,
      prev.total_store_revenue_usd AS previous_store_revenue_usd
      FROM
      store_months AS curr
      JOIN max_months AS m ON curr.storeid = m.storeid
      AND curr.activity_month = m.max_month
      LEFT JOIN store_months AS prev ON curr.storeid = prev.storeid
      AND prev.activity_month = DATEADD (MONTH, -1, curr.activity_month)
      )
      SELECT
      storeid,
      current_month,
      current_rows,
      current_orchard_streams,
      current_store_streams,
      current_gross,
      current_units,
      current_store_revenue_usd,
      previous_month,
      previous_rows,
      previous_orchard_streams,
      previous_store_streams,
      previous_gross,
      previous_units,
      previous_store_revenue_usd,
      (
      (current_rows - previous_rows) / NULLIF(previous_rows, 0)
      ) * 100 AS rows_pct_change,
      (
      (
      current_orchard_streams - previous_orchard_streams
      ) / NULLIF(previous_orchard_streams, 0)
      ) * 100 AS orchard_streams_pct_change,
      (
      (current_store_streams - previous_store_streams) / NULLIF(previous_store_streams, 0)
      ) * 100 AS store_streams_pct_change,
      (
      (current_gross - previous_gross) / NULLIF(previous_gross, 0)
      ) * 100 AS gross_pct_change,
      (
      (current_units - previous_units) / NULLIF(previous_units, 0)
      ) * 100 AS units_pct_change,
      (
      (
      current_store_revenue_usd - previous_store_revenue_usd
      ) / NULLIF(previous_store_revenue_usd, 0)
      ) * 100 AS revenue_pct_change
      FROM
      metrics_comparison
      ORDER BY
      storeid ;;
```