TPC-DI AQ10

TPC-DI benchmark · all TPC-DI queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  'Regulatory Compliance and Risk Monitoring' AS analysis_name,
  c.SK_CustomerID,
  c.Tier,
  c.Country,
  COUNT(t.TradeID) AS total_trades,
  SUM(t.Quantity * t.TradePrice) AS total_trade_value,
  MAX(t.Quantity * t.TradePrice) AS largest_single_trade,
  COUNT(CASE WHEN t.Quantity * t.TradePrice > 100000.0 THEN 1 END) AS large_trades,
  COUNT(CASE WHEN d.DateValue = CURRENT_DATE THEN 1 END) AS same_day_trades,
  COUNT(DISTINCT t.SK_SecurityID) AS securities_traded,
  SUM(h.CurrentHolding * h.CurrentPrice) AS current_portfolio_value,
  SUM(h.CurrentHolding * h.CurrentPrice) / c.NetWorth * 100 AS portfolio_to_networth_pct,
  COUNT(CASE WHEN wash_sales.wash_sale_flag = 1 THEN 1 END) AS potential_wash_sales,
  AVG(t.Commission) / AVG(t.Quantity * t.TradePrice) * 100 AS avg_commission_rate_pct,
  CASE
    WHEN COUNT(CASE WHEN t.Quantity * t.TradePrice > 100000.0 THEN 1 END) > 10
    THEN 'HIGH RISK'
    WHEN SUM(h.CurrentHolding * h.CurrentPrice) / c.NetWorth > 0.8
    THEN 'CONCENTRATION RISK'
    WHEN COUNT(CASE WHEN wash_sales.wash_sale_flag = 1 THEN 1 END) > 0
    THEN 'WASH SALE RISK'
    WHEN COUNT(CASE WHEN d.DateValue = CURRENT_DATE THEN 1 END) > 4
    THEN 'DAY TRADING RISK'
    ELSE 'NORMAL'
  END AS risk_profile
FROM DimCustomer AS c
LEFT JOIN FactTrade AS t
  ON c.SK_CustomerID = t.SK_CustomerID
LEFT JOIN FactHoldings AS h
  ON c.SK_CustomerID = h.SK_CustomerID
LEFT JOIN DimDate AS d
  ON t.SK_CreateDateID = d.SK_DateID
LEFT JOIN (
  SELECT
    t1.SK_CustomerID,
    t1.SK_SecurityID,
    t1.TradeID,
    CASE WHEN t2.TradeID IS NOT NULL THEN 1 ELSE 0 END AS wash_sale_flag
  FROM FactTrade AS t1
  LEFT JOIN DimDate AS t1_date
    ON t1.SK_CreateDateID = t1_date.SK_DateID
  LEFT JOIN FactTrade AS t2
    ON t1.SK_CustomerID = t2.SK_CustomerID
    AND t1.SK_SecurityID = t2.SK_SecurityID
    AND t1.TradeID <> t2.TradeID
  LEFT JOIN DimDate AS t2_date
    ON t2.SK_CreateDateID = t2_date.SK_DateID
    AND ABS((
      t1_date.DateValue - t2_date.DateValue
    )) <= 30
) AS wash_sales
  ON t.TradeID = wash_sales.TradeID
WHERE
  c.IsCurrent IS TRUE AND t.Status = 'Completed' AND d.CalendarYearID >= 2015
GROUP BY
  c.SK_CustomerID,
  c.Tier,
  c.Country,
  c.NetWorth
HAVING
  COUNT(t.TradeID) > 10
ORDER BY
  CASE risk_profile
    WHEN 'HIGH RISK'
    THEN 1
    WHEN 'CONCENTRATION RISK'
    THEN 2
    WHEN 'WASH SALE RISK'
    THEN 3
    WHEN 'DAY TRADING RISK'
    THEN 4
    ELSE 5
  END,
  total_trade_value DESC
LIMIT 50

Representative DataFrame

No DataFrame implementation is registered for this query.

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import TPCDI

bench = TPCDI(scale_factor=0.01)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['AQ10'])

Source

Query text: benchbox/core/tpcdi/query_analytics.py