Query Patterns

This guide provides copy-paste SQL examples for common analytical tasks in the EWE Numera platform.

Connection Setup

Python

import snowflake.connector
import pandas as pd

conn = snowflake.connector.connect(
    user="your_user",
    password="your_password",
    account="your_account.snowflakecomputing.com",
    database="NUMERA_EWE_PROD_CORE",
    schema="NUMERA_DIM_MODEL",
    warehouse="NUMERA_PROD_REPORTING",
    role="NUMERA_EWE_PROD_REPORTER"
)

# Execute query and return DataFrame
def query_to_df(sql):
    cur = conn.cursor()
    cur.execute(sql)
    return cur.fetch_pandas_all()

---

P&L Analysis

Daily P&L by Portfolio

SELECT
    f.metric_date,
    d.portfolio,
    SUM(f.pnl_unrealized_ltd) AS unrealized_pnl,
    SUM(f.pnl_realized_ltd) AS realized_pnl,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date >= DATEADD('day', -30, CURRENT_DATE)
  AND d.dim_deal_attribute_id != 0
GROUP BY f.metric_date, d.portfolio
ORDER BY f.metric_date DESC, d.portfolio

P&L by Counterparty

SELECT
    pe.lentity_name AS counterparty,
    d.instrument,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0
  AND pe.dim_party_external_bu_le_attribute_id != 0
GROUP BY pe.lentity_name, d.instrument
ORDER BY total_pnl DESC

P&L by Delivery Month

SELECT
    f.delivery_month,
    d.portfolio,
    SUM(f.pnl_ltd) AS total_pnl
FROM fct_pnl f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND d.dim_deal_attribute_id != 0
  AND f.delivery_month >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY f.delivery_month, d.portfolio
ORDER BY f.delivery_month, d.portfolio

---

Credit Exposure Analysis

Current Exposure by Counterparty

SELECT
    pe.lentity_name AS counterparty,
    f.exp_line_id,
    f.current_exposure,
    f.potential_exposure,
    f.exposure_limit,
    CASE
        WHEN f.exposure_limit > 0
        THEN ROUND(f.current_exposure / f.exposure_limit * 100, 2)
        ELSE NULL
    END AS utilization_pct
FROM fct_credit_exposure f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
ORDER BY f.current_exposure DESC

Monthly Exposure Projection

SELECT
    f.effective_month,
    pe.lentity_name AS counterparty,
    f.potential_exposure_low,
    f.potential_exposure_high,
    f.pot_performance_exposure_low,
    f.pot_performance_exposure_high
FROM fct_credit_exposure_month f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND f.effective_month BETWEEN CURRENT_DATE AND DATEADD('month', 12, CURRENT_DATE)
ORDER BY pe.lentity_name, f.effective_month

PEX Factors by Commodity

SELECT
    metric_date,
    commodity,
    factor AS pex_factor
FROM fct_credit_pex_factor
WHERE metric_date = CURRENT_DATE - 1
ORDER BY commodity

Exposure Trend Over Time

SELECT
    f.metric_date,
    pe.lentity_name AS counterparty,
    f.current_exposure,
    f.potential_exposure
FROM fct_credit_exposure f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date >= DATEADD('day', -30, CURRENT_DATE)
  AND pe.lentity_name = 'Your Counterparty Name'
ORDER BY f.metric_date

---

Clearing Statement Analysis

Open Positions by Clearing Bank

SELECT
    pe.bunit_name AS clearing_bank,
    f.exchange,
    f.symbol,
    f.buy_sell,
    SUM(f.quantity) AS total_contracts,
    SUM(f.volume) AS total_volume,
    SUM(f.ote) AS total_ote,
    SUM(f.variation_margin) AS total_var_margin
FROM fct_clearing_statement_open_position f
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY pe.bunit_name, f.exchange, f.symbol, f.buy_sell
ORDER BY pe.bunit_name, f.exchange, f.symbol

Daily Position Changes

WITH today AS (
    SELECT * FROM fct_clearing_statement_open_position
    WHERE metric_date = CURRENT_DATE - 1
),
yesterday AS (
    SELECT * FROM fct_clearing_statement_open_position
    WHERE metric_date = CURRENT_DATE - 2
)
SELECT
    t.exchange,
    t.symbol,
    t.buy_sell,
    COALESCE(t.quantity, 0) AS today_qty,
    COALESCE(y.quantity, 0) AS yesterday_qty,
    COALESCE(t.quantity, 0) - COALESCE(y.quantity, 0) AS qty_change,
    t.settlement_price,
    t.variation_margin
FROM today t
FULL OUTER JOIN yesterday y
    ON t.exchange = y.exchange
    AND t.symbol = y.symbol
    AND t.buy_sell = y.buy_sell
WHERE COALESCE(t.quantity, 0) != COALESCE(y.quantity, 0)
ORDER BY ABS(COALESCE(t.quantity, 0) - COALESCE(y.quantity, 0)) DESC

---

Power Volume Analysis

15-Minute Volumes by Business Case

SELECT
    delivery_date_utc,
    business_case,
    grid,
    SUM(volume_buy_mw) AS total_buy_mw,
    SUM(volume_sell_mw) AS total_sell_mw,
    SUM(volume_buy_mw) - SUM(volume_sell_mw) AS net_position_mw
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY delivery_date_utc, business_case, grid
ORDER BY business_case, grid

Hourly Volume Profile

SELECT
    DATE_TRUNC('hour', begin_cet) AS hour_cet,
    business_case,
    SUM(volume_buy_mw) AS buy_mw,
    SUM(volume_sell_mw) AS sell_mw,
    AVG(buy_vwap_price_eur_per_mwh) AS avg_buy_price,
    AVG(sell_vwap_price_eur_per_mwh) AS avg_sell_price
FROM fct_ewe_volume_pwr_by_business_case
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY DATE_TRUNC('hour', begin_cet), business_case
ORDER BY hour_cet, business_case

Volume by Deal

SELECT
    delivery_date_utc,
    deal_id,
    business_case,
    grid,
    SUM(volume_buy_mw) AS buy_mw,
    SUM(volume_sell_mw) AS sell_mw,
    AVG(buy_vwap_price_eur_per_mwh) AS avg_buy_price,
    AVG(sell_vwap_price_eur_per_mwh) AS avg_sell_price
FROM fct_ewe_volume_pwr_by_business_case_deal
WHERE delivery_date_utc = CURRENT_DATE
GROUP BY delivery_date_utc, deal_id, business_case, grid
ORDER BY deal_id

---

Volume Analysis

Daily Scheduled Volumes by Location

SELECT
    DATE(f.start_time_utc) AS delivery_date,
    dl.location_name AS delivery_location,
    d.portfolio,
    d.buy_sell,
    SUM(f.volume_abs) AS total_volume
FROM fct_schedule f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_point_of_delivery_location dl
    ON f.dim_point_of_delivery_location_id = dl.dim_point_of_delivery_location_id
WHERE f.start_time_utc >= CURRENT_DATE
  AND f.start_time_utc < DATEADD('day', 7, CURRENT_DATE)
  AND f.is_best_available = TRUE
  AND d.dim_deal_attribute_id != 0
GROUP BY DATE(f.start_time_utc), dl.location_name, d.portfolio, d.buy_sell
ORDER BY delivery_date, dl.location_name

Buy vs Sell Volume Summary

SELECT
    d.portfolio,
    SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_abs ELSE 0 END) AS buy_volume,
    SUM(CASE WHEN d.buy_sell = 'Sell' THEN f.volume_abs ELSE 0 END) AS sell_volume,
    SUM(CASE WHEN d.buy_sell = 'Buy' THEN f.volume_abs ELSE -f.volume_abs END) AS net_volume
FROM fct_schedule f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.start_time_utc >= DATE_TRUNC('month', CURRENT_DATE)
  AND f.start_time_utc < DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE))
  AND f.is_best_available = TRUE
  AND d.dim_deal_attribute_id != 0
GROUP BY d.portfolio
ORDER BY d.portfolio

---

Deal Analysis

Active Deals by Status

SELECT
    s.tran_status,
    d.portfolio,
    COUNT(DISTINCT d.deal_id) AS deal_count
FROM fct_deal f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_tran_status s
    ON f.dim_tran_status_id = s.dim_tran_status_id
WHERE s.tran_status IN ('New', 'Validated', 'Amended', 'Matured')
GROUP BY s.tran_status, d.portfolio
ORDER BY d.portfolio, s.tran_status

Deals by Counterparty

SELECT
    pe.lentity_name AS counterparty,
    d.instrument,
    COUNT(DISTINCT d.deal_id) AS deal_count
FROM fct_deal f
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
LEFT JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
LEFT JOIN dim_tran_status s
    ON f.dim_tran_status_id = s.dim_tran_status_id
WHERE s.tran_status IN ('Validated', 'Amended', 'Matured')
  AND pe.dim_party_external_bu_le_attribute_id != 0
GROUP BY pe.lentity_name, d.instrument
ORDER BY deal_count DESC

---

Risk Analysis

Delta Exposure by Index

SELECT
    ia.index_name,
    d.portfolio,
    f.delivery_month,
    SUM(f.delta_mwh) AS total_delta_mwh
FROM fct_delta f
LEFT JOIN dim_index_attribute ia
    ON f.dim_index_attribute_id = ia.dim_index_attribute_id
LEFT JOIN dim_deal_attribute d
    ON f.dim_deal_attribute_id = d.dim_deal_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
  AND f.delivery_month >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY ia.index_name, d.portfolio, f.delivery_month
ORDER BY ia.index_name, d.portfolio, f.delivery_month

VaR by Portfolio

SELECT
    f.metric_date,
    p.portfolio_name,
    f.var_95,
    f.var_99
FROM fct_var_pfolio f
LEFT JOIN dim_portfolio p
    ON f.dim_portfolio_id = p.dim_portfolio_id
WHERE f.metric_date >= DATEADD('day', -30, CURRENT_DATE)
ORDER BY f.metric_date DESC, p.portfolio_name

---

Price Data

Index Price History

SELECT
    f.price_date,
    ia.index_name,
    f.close_price,
    f.settlement_price
FROM fct_index_price_history f
LEFT JOIN dim_index_attribute ia
    ON f.dim_index_attribute_id = ia.dim_index_attribute_id
WHERE ia.index_name IN ('TTF', 'NBP', 'EEX_BASE')
  AND f.price_date >= DATEADD('day', -30, CURRENT_DATE)
ORDER BY ia.index_name, f.price_date

---

Tips and Best Practices

Filtering for Active Deals

Always filter to relevant transaction statuses for business reporting:

WHERE tran_status IN ('Validated', 'Matured', 'Amended')

Excluding Placeholder Rows

Dimension ID = 0 represents unknown/not applicable. Exclude for complete data:

WHERE dim_deal_attribute_id != 0
  AND dim_party_external_bu_le_attribute_id != 0

Using Best Available Data

For schedule data, always use the best available flag:

WHERE is_best_available = TRUE

Date Range Filtering

Apply date filters early in the query for better performance:

WHERE metric_date >= DATEADD('day', -30, CURRENT_DATE)
  AND metric_date < CURRENT_DATE

Performance Tips

1. Filter early: Apply WHERE clauses before JOINs when possible
2. Use surrogate keys: Join on integer IDs, not natural keys
3. Limit results: Use LIMIT during development
4. Aggregate at source: Push aggregations into the database