| Actor | Fragment | Worker | State |
|---|---|---|---|
| 53699 | 2513 | 3 | running |
| 53700 | 2513 | 3 | running |
| 53708 | 2508 | 3 | running |
| 53709 | 2508 | 3 | running |
| 53710 | 2509 | 3 | running |
| 53711 | 2509 | 3 | running |
| 53714 | 2518 | 3 | running |
| 53715 | 2518 | 3 | running |
| 53716 | 2511 | 3 | running |
| 53717 | 2511 | 3 | running |
| 53718 | 2510 | 3 | running |
| 53719 | 2510 | 3 | running |
CREATE MATERIALIZED VIEW insights.client_account_via_portfolio_mv AS
SELECT
client_id,
account_id,
effective_start_date,
effective_end_date,
type
FROM (
SELECT
cp.client_id,
atp.account_id,
GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
CASE
WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
THEN CAST(NULL AS DATE)
WHEN atp.effective_end_date IS NULL
THEN cp.effective_end_date
WHEN cp.effective_end_date IS NULL
THEN atp.effective_end_date
ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
END AS effective_end_date,
CAST('all' AS VARCHAR) AS type
FROM olap.account_to_portfolios_dm AS atp
JOIN olap.clients_portfolios_dm AS cp
ON cp.portfolio_id = atp.portfolio_id
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND cp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
AND NOT EXISTS(
SELECT
1
FROM olap.accounts_to_clients_dm AS atc
WHERE
atc.account_id = atp.account_id
AND atc.client_id = cp.client_id
AND atc.disabled_at IS NULL
AND (
atc.effective_end_date IS NULL
OR atc.effective_end_date > atc.effective_start_date
)
)
UNION ALL
SELECT
cp.client_id,
atp.account_id,
GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
CASE
WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
THEN CAST(NULL AS DATE)
WHEN atp.effective_end_date IS NULL
THEN cp.effective_end_date
WHEN cp.effective_end_date IS NULL
THEN atp.effective_end_date
ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
END AS effective_end_date,
CAST('restricted' AS VARCHAR) AS type
FROM olap.account_to_portfolios_dm AS atp
JOIN olap.clients_portfolios_dm AS cp
ON cp.portfolio_id = atp.portfolio_id
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND cp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
AND NOT EXISTS(
SELECT
1
FROM olap.accounts_to_clients_dm AS atc
WHERE
atc.account_id = atp.account_id
AND atc.client_id = cp.client_id
AND atc.disabled_at IS NULL
AND (
atc.effective_end_date IS NULL
OR atc.effective_end_date > atc.effective_start_date
)
)
AND oa.is_restricted = TRUE
UNION ALL
SELECT
cp.client_id,
atp.account_id,
GREATEST(atp.effective_start_date, cp.effective_start_date) AS effective_start_date,
CASE
WHEN atp.effective_end_date IS NULL AND cp.effective_end_date IS NULL
THEN CAST(NULL AS DATE)
WHEN atp.effective_end_date IS NULL
THEN cp.effective_end_date
WHEN cp.effective_end_date IS NULL
THEN atp.effective_end_date
ELSE LEAST(atp.effective_end_date, cp.effective_end_date)
END AS effective_end_date,
CAST('un_restricted' AS VARCHAR) AS type
FROM olap.account_to_portfolios_dm AS atp
JOIN olap.clients_portfolios_dm AS cp
ON cp.portfolio_id = atp.portfolio_id
JOIN insights.open_accounts_mv AS oa
ON oa.account_id = atp.account_id
WHERE
atp.disabled_at IS NULL
AND cp.disabled_at IS NULL
AND (
atp.effective_end_date IS NULL
OR atp.effective_end_date > atp.effective_start_date
)
AND NOT EXISTS(
SELECT
1
FROM olap.accounts_to_clients_dm AS atc
WHERE
atc.account_id = atp.account_id
AND atc.client_id = cp.client_id
AND atc.disabled_at IS NULL
AND (
atc.effective_end_date IS NULL
OR atc.effective_end_date > atc.effective_start_date
)
)
AND NOT oa.is_restricted IS TRUE
) AS intervals
WHERE
effective_end_date IS NULL OR effective_start_date < effective_end_date