view: market_share_fact_sales_test {
  derived_table: {
    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 ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  dimension: storeid {
    type: number
    sql: ${TABLE}."STOREID" ;;
  }

  dimension: current_month {
    type: date
    sql: ${TABLE}."CURRENT_MONTH" ;;
  }

  dimension: current_rows {
    type: number
    sql: ${TABLE}."CURRENT_ROWS" ;;
  }

  dimension: current_orchard_streams {
    type: number
    sql: ${TABLE}."CURRENT_ORCHARD_STREAMS" ;;
  }

  dimension: current_store_streams {
    type: number
    sql: ${TABLE}."CURRENT_STORE_STREAMS" ;;
  }

  dimension: current_gross {
    type: number
    sql: ${TABLE}."CURRENT_GROSS" ;;
  }

  dimension: current_units {
    type: number
    sql: ${TABLE}."CURRENT_UNITS" ;;
  }

  dimension: current_store_revenue_usd {
    type: number
    sql: ${TABLE}."CURRENT_STORE_REVENUE_USD" ;;
  }

  dimension: previous_month {
    type: date
    sql: ${TABLE}."PREVIOUS_MONTH" ;;
  }

  dimension: previous_rows {
    type: number
    sql: ${TABLE}."PREVIOUS_ROWS" ;;
  }

  dimension: previous_orchard_streams {
    type: number
    sql: ${TABLE}."PREVIOUS_ORCHARD_STREAMS" ;;
  }

  dimension: previous_store_streams {
    type: number
    sql: ${TABLE}."PREVIOUS_STORE_STREAMS" ;;
  }

  dimension: previous_gross {
    type: number
    sql: ${TABLE}."PREVIOUS_GROSS" ;;
  }

  dimension: previous_units {
    type: number
    sql: ${TABLE}."PREVIOUS_UNITS" ;;
  }

  dimension: previous_store_revenue_usd {
    type: number
    sql: ${TABLE}."PREVIOUS_STORE_REVENUE_USD" ;;
  }

  dimension: rows_pct_change {
    type: number
    sql: ${TABLE}."ROWS_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  dimension: orchard_streams_pct_change {
    type: number
    sql: ${TABLE}."ORCHARD_STREAMS_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  dimension: store_streams_pct_change {
    type: number
    sql: ${TABLE}."STORE_STREAMS_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  dimension: gross_pct_change {
    type: number
    sql: ${TABLE}."GROSS_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  dimension: units_pct_change {
    type: number
    sql: ${TABLE}."UNITS_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  dimension: revenue_pct_change {
    type: number
    sql: ${TABLE}."REVENUE_PCT_CHANGE" ;;
    html: {% if value > 20 %}

    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% elsif value < -20 %}
    <span style="color: white; background-color: #ea9999; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% else %}
    <span style="color: white; background-color: #72c275; padding: 2px 6px; border-radius: 4px;">{{ value | round: 2 }}</span>
    {% endif %};;
  }

  set: detail {
    fields: [
        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,
  rows_pct_change,
  orchard_streams_pct_change,
  store_streams_pct_change,
  gross_pct_change,
  units_pct_change,
  revenue_pct_change
    ]
  }
}