TPC-DI VQ6

TPC-DI benchmark · all TPC-DI queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  'Trade-Holdings Consistency Validation' AS validation_name,
  customer_id,
  security_id,
  total_buy_quantity,
  total_sell_quantity,
  net_position,
  current_holdings,
  position_discrepancy,
  CASE WHEN ABS(position_discrepancy) <= 100 THEN 'PASS' ELSE 'FAIL' END AS status
FROM (
  SELECT
    customer_id,
    security_id,
    total_buy_quantity,
    total_sell_quantity,
    net_position,
    current_holdings,
    net_position - current_holdings AS position_discrepancy
  FROM (
    SELECT
      trade_rows.SK_CustomerID AS customer_id,
      trade_rows.SK_SecurityID AS security_id,
      SUM(trade_rows.buy_quantity) AS total_buy_quantity,
      SUM(trade_rows.sell_quantity) AS total_sell_quantity,
      SUM(trade_rows.signed_quantity) AS net_position,
      COALESCE(fh.CurrentHolding, 0) AS current_holdings
    FROM (
      SELECT
        ft.SK_CustomerID,
        ft.SK_SecurityID,
        CASE WHEN tt.TT_IS_SELL IS FALSE THEN ft.Quantity ELSE 0 END AS buy_quantity,
        CASE WHEN tt.TT_IS_SELL IS TRUE THEN ft.Quantity ELSE 0 END AS sell_quantity,
        CASE WHEN tt.TT_IS_SELL IS FALSE THEN ft.Quantity ELSE -ft.Quantity END AS signed_quantity
      FROM FactTrade AS ft
      JOIN TradeType AS tt
        ON ft.Type = tt.TT_ID
      WHERE
        ft.Status = 'Completed'
    ) AS trade_rows
    LEFT JOIN FactHoldings AS fh
      ON trade_rows.SK_CustomerID = fh.SK_CustomerID
      AND trade_rows.SK_SecurityID = fh.SK_SecurityID
    GROUP BY
      trade_rows.SK_CustomerID,
      trade_rows.SK_SecurityID,
      fh.CurrentHolding
  ) AS grouped_positions
) AS position_check
WHERE
  ABS(position_discrepancy) > 0
ORDER BY
  ABS(position_discrepancy) DESC
LIMIT 100

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['VQ6'])

Source

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