CREATE MATERIALIZED VIEW opportunity.portfolio_idle_cash_breaches_mv AS
WITH conditions AS (
SELECT
opportunity_id,
activity_name,
op,
threshold,
interval_to_check_days,
as_of_date
FROM opportunity.opportunity_conditions_mv AS opportunity_conditions_mv_next
WHERE
activity_name = 'GET_PORTFOLIO_IDLE_CASH_PERCENTAGE'
AND NOT as_of_date IS NULL
AND NOT interval_to_check_days IS NULL
), breaching_days AS (
SELECT
c.opportunity_id,
r.portfolio_id,
c.as_of_date,
c.activity_name,
c.interval_to_check_days,
r.dim_value_date,
r.cash_ratio,
r.cash_value,
r.currency_code
FROM opportunity.portfolio_cash_ratio_mv AS r
JOIN conditions AS c
ON c.activity_name = r.activity_name
WHERE
r.dim_value_date <= c.as_of_date
AND r.dim_value_date > c.as_of_date - c.interval_to_check_days
AND CASE c.op
WHEN 'GTE'
THEN r.cash_ratio >= c.threshold
WHEN 'GT'
THEN r.cash_ratio > c.threshold
WHEN 'LTE'
THEN r.cash_ratio <= c.threshold
WHEN 'LT'
THEN r.cash_ratio < c.threshold
WHEN 'EQ'
THEN r.cash_ratio = c.threshold
ELSE FALSE
END
), sustained AS (
SELECT
opportunity_id,
portfolio_id,
as_of_date,
activity_name,
interval_to_check_days,
MAX(dim_value_date) AS latest_date,
MAX(currency_code) AS currency_code
FROM breaching_days
GROUP BY
opportunity_id,
portfolio_id,
as_of_date,
activity_name,
interval_to_check_days
HAVING
COUNT(*) = MAX(interval_to_check_days)
), account_recently_traded AS (
SELECT DISTINCT
account_id,
transaction_valuation_date
FROM olap.transactions_dm
WHERE
disabled_at IS NULL
AND CAST(transaction_valuation_date AS TIMESTAMPTZ) >= CURRENT_TIMESTAMP - INTERVAL '30 DAYS'
), portfolio_traded_days AS (
SELECT
atp.portfolio_id,
t.transaction_valuation_date,
'GET_PORTFOLIO_IDLE_CASH_PERCENTAGE' AS activity_name
FROM account_recently_traded AS t
JOIN olap.account_to_portfolios_dm AS atp
ON atp.account_id = t.account_id
AND atp.disabled_at IS NULL
AND atp.effective_end_date IS NULL
GROUP BY
atp.portfolio_id,
t.transaction_valuation_date
), traded_in_window AS (
SELECT DISTINCT
c.opportunity_id,
p.portfolio_id
FROM portfolio_traded_days AS p
JOIN conditions AS c
ON c.activity_name = p.activity_name
WHERE
p.transaction_valuation_date <= c.as_of_date
AND p.transaction_valuation_date > c.as_of_date - c.interval_to_check_days
)
SELECT
s.opportunity_id,
s.portfolio_id AS resource_id,
s.as_of_date AS fact_date,
s.activity_name,
s.latest_date,
s.interval_to_check_days,
r.cash_ratio,
r.cash_value AS idle_cash_amount,
s.currency_code AS currency
FROM sustained AS s
JOIN opportunity.portfolio_cash_ratio_mv AS r
ON r.portfolio_id = s.portfolio_id AND r.dim_value_date = s.latest_date
LEFT JOIN traded_in_window AS tw
ON tw.opportunity_id = s.opportunity_id AND tw.portfolio_id = s.portfolio_id
WHERE
tw.portfolio_id IS NULL