TPC-DI VQ12

TPC-DI benchmark · all TPC-DI queries

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  'Cash Balance Consistency Validation' AS validation_name,
  cb.SK_CustomerID,
  cb.SK_AccountID,
  cb.Cash AS current_cash_balance,
  SUM(
    CASE
      WHEN tt.TT_IS_SELL IS TRUE
      THEN ft.Quantity * ft.TradePrice - ft.Fee - ft.Commission - ft.Tax
      WHEN tt.TT_IS_SELL IS FALSE
      THEN -(
        ft.Quantity * ft.TradePrice + ft.Fee + ft.Commission + ft.Tax
      )
      ELSE 0
    END
  ) AS calculated_cash_impact,
  ABS(
    cb.Cash - SUM(
      CASE
        WHEN tt.TT_IS_SELL IS TRUE
        THEN ft.Quantity * ft.TradePrice - ft.Fee - ft.Commission - ft.Tax
        WHEN tt.TT_IS_SELL IS FALSE
        THEN -(
          ft.Quantity * ft.TradePrice + ft.Fee + ft.Commission + ft.Tax
        )
        ELSE 0
      END
    )
  ) AS balance_discrepancy,
  CASE
    WHEN ABS(
      cb.Cash - SUM(
        CASE
          WHEN tt.TT_IS_SELL IS TRUE
          THEN ft.Quantity * ft.TradePrice - ft.Fee - ft.Commission - ft.Tax
          WHEN tt.TT_IS_SELL IS FALSE
          THEN -(
            ft.Quantity * ft.TradePrice + ft.Fee + ft.Commission + ft.Tax
          )
          ELSE 0
        END
      )
    ) <= 1000.0
    THEN 'PASS'
    ELSE 'FAIL'
  END AS status
FROM FactCashBalances AS cb
LEFT JOIN FactTrade AS ft
  ON cb.SK_CustomerID = ft.SK_CustomerID AND cb.SK_AccountID = ft.SK_AccountID
LEFT JOIN TradeType AS tt
  ON ft.Type = tt.TT_ID
WHERE
  ft.Status = 'Completed'
GROUP BY
  cb.SK_CustomerID,
  cb.SK_AccountID,
  cb.Cash
HAVING
  COUNT(ft.TradeID) > 0
ORDER BY
  balance_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['VQ12'])

Source

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