view: market_share_test {
derived_table: {
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 ;;
}
measure: count {
type: count
drill_fields: [detail*]
}
dimension: store {
type: string
sql: ${TABLE}."STORE" ;;
}
dimension: max_month {
type: date
sql: ${TABLE}."MAX_MONTH" ;;
}
dimension: previous_month {
type: date
sql: ${TABLE}."PREVIOUS_MONTH" ;;
}
dimension: max_month_rows {
type: number
sql: ${TABLE}."MAX_MONTH_ROWS" ;;
}
dimension: previous_month_rows {
type: number
sql: ${TABLE}."PREVIOUS_MONTH_ROWS" ;;
}
dimension: max_month_volume {
type: number
sql: ${TABLE}."MAX_MONTH_VOLUME" ;;
}
dimension: previous_month_volume {
type: number
sql: ${TABLE}."PREVIOUS_MONTH_VOLUME" ;;
}
dimension: total_store_volume {
type: number
sql: ${TABLE}."TOTAL_STORE_VOLUME" ;;
}
dimension: store_revenue_usd {
type: number
sql: ${TABLE}."STORE_REVENUE_USD" ;;
}
dimension: orchard_gross_revenue_usd {
type: number
sql: ${TABLE}."ORCHARD_GROSS_REVENUE_USD" ;;
}
dimension: prev_month_store_volume {
type: number
sql: ${TABLE}."PREV_MONTH_STORE_VOLUME" ;;
}
dimension: prev_month_store_revenue {
type: number
sql: ${TABLE}."PREV_MONTH_STORE_REVENUE" ;;
}
dimension: prev_month_orchard_revenue {
type: number
sql: ${TABLE}."PREV_MONTH_ORCHARD_REVENUE" ;;
}
dimension: store_volume_pct_change {
type: number
sql: ${TABLE}."STORE_VOLUME_PCT_CHANGE" ;;
html: {% if value > 20 %}
{{ value | round: 2 }}%
{% elsif value < -20 %}
{{ value | round: 2 }}%
{% else %}
{{ value | round: 2 }}%
{% endif %};;
}
dimension: store_revenue_pct_change {
type: number
sql: ${TABLE}."STORE_REVENUE_PCT_CHANGE" ;;
html: {% if value > 20 %}
{{ value | round: 2 }}%
{% elsif value < -20 %}
{{ value | round: 2 }}%
{% else %}
{{ value | round: 2 }}%
{% endif %};;
}
dimension: orchard_revenue_pct_change {
type: number
sql: ${TABLE}."ORCHARD_REVENUE_PCT_CHANGE" ;;
html: {% if value > 20 %}
{{ value | round: 2 }}%
{% elsif value < -20 %}
{{ value | round: 2 }}%
{% else %}
{{ value | round: 2 }}%
{% endif %};;
}
dimension: row_pct_change {
type: number
sql: ${TABLE}."ROW_PCT_CHANGE" ;;
html: {% if value > 20 %}
{{ value | round: 2 }}%
{% elsif value < -20 %}
{{ value | round: 2 }}%
{% else %}
{{ value | round: 2 }}%
{% endif %};;
}
dimension: volume_pct_change {
type: number
sql: ${TABLE}."VOLUME_PCT_CHANGE" ;;
html: {% if value > 20 %}
{{ value | round: 2 }}%
{% elsif value < -20 %}
{{ value | round: 2 }}%
{% else %}
{{ value | round: 2 }}%
{% endif %};;
}
set: detail {
fields: [
store,
max_month,
previous_month,
max_month_rows,
previous_month_rows,
max_month_volume,
previous_month_volume,
row_pct_change,
volume_pct_change,
total_store_volume,
store_revenue_usd,
orchard_gross_revenue_usd,
prev_month_store_volume,
prev_month_store_revenue,
prev_month_orchard_revenue,
store_volume_pct_change,
store_revenue_pct_change,
orchard_revenue_pct_change
]
}
}