CREATE MATERIALIZED VIEW insights.account_to_account_groups_settlement_binned_mv AS
SELECT
atag.account_id,
atag.account_group_id,
atag.effective_start_date,
atag.effective_end_date,
atag.base_currency,
atag.opening_date,
atag.source_entity_type,
spine.dim_settlement_month
FROM insights.account_to_account_groups_mv AS atag
JOIN (
SELECT DISTINCT
account_id,
CAST(DATE_TRUNC('MONTH', transaction_settlement_date) AS DATE) AS dim_settlement_month
FROM insights.transactions_merged_mv
WHERE
NOT transaction_settlement_date IS NULL
) AS spine
ON spine.account_id = atag.account_id
AND spine.dim_settlement_month >= CAST(DATE_TRUNC('MONTH', atag.effective_start_date) AS DATE)
AND (
atag.effective_end_date IS NULL
OR spine.dim_settlement_month < atag.effective_end_date
)