CREATE MATERIALIZED VIEW insights.intraday_position_values_mv AS
WITH eod_base AS (
SELECT
account_id,
asset_id,
currency_code,
dim_value_date,
purchased_quantity,
average_cost_per_unit,
market_value
FROM (
SELECT
account_id,
asset_id,
currency_code,
dim_value_date,
purchased_quantity,
average_cost_per_unit,
market_value,
ROW_NUMBER() OVER (PARTITION BY account_id, asset_id ORDER BY type) AS rn
FROM insights.holding_values_latest_mv
)
WHERE
rn = 1
), settled_gap AS (
SELECT
s.account_id,
s.asset_id,
s.currency_code,
s.purchased_quantity,
s.average_cost_per_unit,
s.market_value
FROM insights.intraday_settled_baseline_mv AS s
LEFT JOIN eod_base AS e
ON e.account_id = s.account_id AND e.asset_id = s.asset_id
WHERE
e.account_id IS NULL OR s.dim_value_date > e.dim_value_date
), combined_keys AS (
SELECT
account_id,
asset_id
FROM (
SELECT
account_id,
asset_id
FROM insights.intraday_holdings_latest_mv
UNION ALL
SELECT
account_id,
asset_id
FROM settled_gap
UNION ALL
SELECT
account_id,
asset_id
FROM eod_base
)
GROUP BY
account_id,
asset_id
), combined AS (
SELECT
k.account_id,
k.asset_id,
COALESCE(hl.currency_code, g.currency_code, e.currency_code) AS currency_code,
COALESCE(hl.purchased_quantity, g.purchased_quantity, e.purchased_quantity) AS purchased_quantity,
COALESCE(hl.average_cost_per_unit, g.average_cost_per_unit, e.average_cost_per_unit, 0) AS average_cost_per_unit,
CASE
WHEN COALESCE(hl.purchased_quantity, g.purchased_quantity, e.purchased_quantity) = 0
THEN CAST(0 AS DECIMAL)
ELSE COALESCE(hl.market_value, g.market_value, e.market_value)
END AS market_value,
ev.event_time AS holding_ts
FROM combined_keys AS k
LEFT JOIN insights.intraday_holdings_latest_mv AS hl
ON hl.account_id = k.account_id AND hl.asset_id = k.asset_id
LEFT JOIN settled_gap AS g
ON g.account_id = k.account_id AND g.asset_id = k.asset_id
LEFT JOIN eod_base AS e
ON e.account_id = k.account_id AND e.asset_id = k.asset_id
LEFT JOIN insights.intraday_holding_event_times_mv AS ev
ON ev.account_id = k.account_id AND ev.asset_id = k.asset_id
)
SELECT
atag.account_group_id,
c.account_id,
c.asset_id,
'POSITION' AS position_type,
c.currency_code AS holding_currency,
atag.base_currency AS group_currency,
a.issue_currency_code AS asset_currency,
COALESCE(a.type, 'UNSPECIFIED') AS asset_type,
COALESCE(c.purchased_quantity * px.last_price, c.market_value) AS market_value,
c.average_cost_per_unit * c.purchased_quantity AS average_cost,
c.purchased_quantity,
CAST(0 AS DECIMAL) AS fair_value,
CAST(0 AS DECIMAL) AS accrued_value,
COALESCE(fx_group.rate, CASE WHEN c.currency_code = atag.base_currency THEN 1 END) AS fx_rate_to_group,
COALESCE(fx_system.rate, CASE WHEN c.currency_code = 'AED' THEN 1 END) AS fx_rate_to_system,
COALESCE(fx_asset.rate, CASE WHEN c.currency_code = a.issue_currency_code THEN 1 END) AS fx_rate_to_asset,
COALESCE(c.purchased_quantity * px.last_price, c.market_value) * COALESCE(fx_group.rate, CASE WHEN c.currency_code = atag.base_currency THEN 1 END) AS market_value_group,
(
c.average_cost_per_unit * c.purchased_quantity
) * COALESCE(fx_group.rate, CASE WHEN c.currency_code = atag.base_currency THEN 1 END) AS average_cost_group,
CAST(0 AS DECIMAL) AS fair_value_group,
CAST(0 AS DECIMAL) AS accrued_value_group,
COALESCE(c.purchased_quantity * px.last_price, c.market_value) * COALESCE(fx_system.rate, CASE WHEN c.currency_code = 'AED' THEN 1 END) AS market_value_system,
(
c.average_cost_per_unit * c.purchased_quantity
) * COALESCE(fx_system.rate, CASE WHEN c.currency_code = 'AED' THEN 1 END) AS average_cost_system,
CAST(0 AS DECIMAL) AS fair_value_system,
CAST(0 AS DECIMAL) AS accrued_value_system,
COALESCE(c.purchased_quantity * px.last_price, c.market_value) * COALESCE(fx_asset.rate, CASE WHEN c.currency_code = a.issue_currency_code THEN 1 END) AS market_value_asset,
(
c.average_cost_per_unit * c.purchased_quantity
) * COALESCE(fx_asset.rate, CASE WHEN c.currency_code = a.issue_currency_code THEN 1 END) AS average_cost_asset,
CAST(0 AS DECIMAL) AS fair_value_asset,
GREATEST(
c.holding_ts,
px.value_timestamp,
fx_group.value_timestamp,
fx_system.value_timestamp,
fx_asset.value_timestamp
) AS change_time
FROM combined AS c
JOIN (
SELECT
account_id,
account_group_id,
base_currency
FROM insights.account_to_account_groups_mv
WHERE
effective_end_date IS NULL
AND (
opening_date IS NULL OR opening_date <= CURRENT_TIMESTAMP
)
AND source_entity_type <> 'user'
) AS atag
ON atag.account_id = c.account_id
LEFT JOIN asset_service.assets_dm AS a
ON a.id = c.asset_id
LEFT JOIN insights.intraday_asset_prices_latest_mv AS px
ON px.asset_id = c.asset_id
LEFT JOIN insights.intraday_fx_rates_latest_mv AS fx_group
ON fx_group.source_currency_code = c.currency_code
AND fx_group.target_currency_code = atag.base_currency
LEFT JOIN insights.intraday_fx_rates_latest_mv AS fx_system
ON fx_system.source_currency_code = c.currency_code
AND fx_system.target_currency_code = 'AED'
LEFT JOIN insights.intraday_fx_rates_latest_mv AS fx_asset
ON fx_asset.source_currency_code = c.currency_code
AND fx_asset.target_currency_code = a.issue_currency_code
WHERE
NOT fx_group.rate IS NULL OR c.currency_code = atag.base_currency