CREATE MATERIALIZED VIEW opportunity.signal_portfolio_unrealized_loss_mv AS
WITH signal_metrics AS (
SELECT
portfolio_id,
dim_balance_date,
'GET_PORTFOLIO_UNREALIZED_LOSS_PERCENTAGE' AS activity_name,
loss_ratio AS metric
FROM opportunity.portfolio_unrealized_loss_delta_mv AS portfolio_unrealized_loss_delta_mv_next
)
SELECT
c.opportunity_id,
c.activity_name,
s.portfolio_id AS resource_id,
s.dim_balance_date AS fact_date
FROM signal_metrics AS s
JOIN opportunity.opportunity_conditions_mv AS c
ON c.activity_name = s.activity_name
WHERE
CASE c.op
WHEN 'GTE'
THEN s.metric >= c.threshold
WHEN 'GT'
THEN s.metric > c.threshold
WHEN 'LTE'
THEN s.metric <= c.threshold
WHEN 'LT'
THEN s.metric < c.threshold
WHEN 'EQ'
THEN s.metric = c.threshold
ELSE FALSE
END