Dashboard Architecture and Layout Design Questions

WITH days AS (
  SELECT generate_series(current_date - 89, current_date, interval '1 day')::date AS day
),
dau_by_day AS (
  SELECT
    event_time::date AS day,
    COUNT(DISTINCT user_id) AS dau
  FROM events
  WHERE event_time >= current_date - 89         -- limit scanned rows
    AND event_time < current_date + 1
  GROUP BY 1
)
SELECT
  d.day AS date,
  COALESCE(b.dau, 0) AS dau,
  ROUND(AVG(COALESCE(b.dau, 0)) OVER (
    ORDER BY d.day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  )::numeric, 2) AS dau_7d_avg
FROM days d
LEFT JOIN dau_by_day b USING (day)
ORDER BY d.day;

-- create materialized view (initial)
CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
  (created_at AT TIME ZONE 'UTC')::date AS day,     -- choose timezone explicitly
  product_id,
  SUM(amount_cents)           AS total_sales_cents,
  COUNT(*)                    AS total_orders,
  (SUM(amount_cents) / NULLIF(COUNT(*),0))::bigint AS avg_order_value_cents
FROM raw_transactions
GROUP BY 1, 2
WITH NO DATA; -- create without populating so we can build index first

-- populate initial data
REFRESH MATERIALIZED VIEW daily_sales_mv;

-- create a unique index required for CONCURRENTLY refresh
CREATE UNIQUE INDEX daily_sales_mv_day_product_idx
  ON daily_sales_mv (day, product_id);

-- create additional index to support common queries (by day)
CREATE INDEX daily_sales_mv_day_idx
  ON daily_sales_mv (day);

view: orders {
  sql_table_name: analytics.orders ;;

  parameter: start_date {
    type: date
    default_value: "{{ _user_attributes['timezone'] ? date: 'now' : '' | date: '%Y-%m-%d' | date_modify('-30 days') }}"
  }

  parameter: end_date {
    type: date
    default_value: "{{ date: 'now' | date: '%Y-%m-%d' }}"
  }

  dimension: in_range {
    type: yesno
    sql: ${TABLE}.order_date BETWEEN {% parameter start_date %} AND {% parameter end_date %} ;;
    hidden: yes
  }

  filter: selected_date_range {
    type: string
    sql: ${in_range} = 'yes' ;;
    hidden: yes
  }

  dimension_group: order_date {
    type: time
    timeframes: [raw, date, week, month]
    sql: ${TABLE}.order_date ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
  }
}

explore: orders {
  always_filter: {
    field: orders.selected_date_range
    value: "yes"
  }
}