Holistics 4 example - Model with custom SQL for computing overlap with a parameter (linked to a filter)

overlap_example.aml.sql

Model order_items_aggr {
  ...
  // Query param declaration
  param order_date_param {
    label: 'Order Created At'
    type: 'datetime'
  }

  query: @sql
    with aggr as (
      select
        oi.order_id
        , count(*) as items_count
      from ecommerce.order_items  oi
      left join ecommerce.orders o on oi.order_id = o.id

      {{ order_date_param }} between o.start_date and o.end_date
      or {% filter(order_date_param) %} o.start_date {% end %}

      group by 1
    )

    select
      items_count
      , count(*) as orders_count
    from aggr
    group by 1
  ;;
}