-- Refactored SQL using CTEs for clarity and potential performance improvements

WITH CTE_EX_DATA AS (

-- Clearing Data

SELECT

CM.REPORTING_FIRM_ID,

MOptD.CONTRACT_MARKET_CODE,

MOptD.FUTURES_EXPIRATION_DATE,

MOptD.REPORT_DATE,

SUM(MOptD.LONG) AS LONG,

SUM(MOptD.SHORT) AS SHORT,

MOptD.OPTION_CLASS_CODE,

MOptD.EXPIRATION_ID_DATE,

MOptD.PUT_CALL_INDICATOR,

CAST(MOptD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE

FROM MEMBER_OPTION_DAY AS MOptD WITH (NOLOCK)

INNER JOIN CLEARING_MEMBERSHIP AS CM WITH (NOLOCK)

ON MOptD.EXCHANGE_INITIALS = CM.EXCHANGE_INITIALS

AND MOptD.CLEARING_MEMBERSHIP_ID = CM.CLEARING_MEMBERSHIP_ID

GROUP BY

CM.REPORTING_FIRM_ID,

MOptD.CONTRACT_MARKET_CODE,

MOptD.FUTURES_EXPIRATION_DATE,

MOptD.REPORT_DATE,

MOptD.OPTION_CLASS_CODE,

MOptD.EXPIRATION_ID_DATE,

MOptD.PUT_CALL_INDICATOR,

MOptD.STRIKE_PRICE

),

CTE_OM_DATA AS (

-- Omni Data

SELECT

AOD.CONTRACT_MARKET_CODE,

AOD.FUTURES_EXPIRATION_DATE,

AOD.REPORT_DATE,

SUM(AOD.LONG) AS LONG,

SUM(AOD.SHORT) AS SHORT,

AOD.OWNER_ID,

AOD.TRADER_SUFFIX,

REPORTING_FIRM.REPORTING_FIRM_ID AS OMNI_RF,

AOD.OPTION_CLASS_CODE,

AOD.EXPIRATION_ID_DATE,

AOD.PUT_CALL_INDICATOR,

CAST(AOD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE

FROM ACCOUNT_OPTION_DAY AS AOD WITH (NOLOCK)

INNER JOIN REPORTING_FIRM WITH (NOLOCK)

ON AOD.OWNER_ID = REPORTING_FIRM.OWNER_ID

AND AOD.TRADER_SUFFIX = REPORTING_FIRM.TRADER_SUFFIX

WHERE AOD.ACCOUNT_TYPE_CODE > 0

GROUP BY

REPORTING_FIRM.REPORTING_FIRM_ID,

AOD.CONTRACT_MARKET_CODE,

AOD.FUTURES_EXPIRATION_DATE,

AOD.REPORT_DATE,

AOD.OWNER_ID,

AOD.TRADER_SUFFIX,

AOD.OPTION_CLASS_CODE,

AOD.EXPIRATION_ID_DATE,

AOD.PUT_CALL_INDICATOR,

AOD.STRIKE_PRICE

),

CTE_LT_DATA AS (

-- LT Data

SELECT

AOD.REPORTING_FIRM_ID,

AOD.CONTRACT_MARKET_CODE,

CM.EXCHANGE_INITIALS + ' - ' + CM.CONTRACT_MARKET_NAME AS CONTRACT_MARKET,

AOD.FUTURES_EXPIRATION_DATE,

AOD.REPORT_DATE,

AOD.OPTION_CLASS_CODE,

AOD.EXPIRATION_ID_DATE,

AOD.PUT_CALL_INDICATOR,

CAST(AOD.STRIKE_PRICE AS decimal(18, 6)) AS STRIKE_PRICE,

SUM(AOD.LONG) AS LONG,

SUM(AOD.SHORT) AS SHORT

FROM ACCOUNT_OPTION_DAY AS AOD WITH (NOLOCK)

INNER JOIN CONTRACT_MARKET AS CM WITH (NOLOCK)

ON AOD.CONTRACT_MARKET_CODE = CM.CONTRACT_MARKET_CODE

GROUP BY

AOD.REPORTING_FIRM_ID,

AOD.CONTRACT_MARKET_CODE,

AOD.FUTURES_EXPIRATION_DATE,

AOD.REPORT_DATE,

AOD.OPTION_CLASS_CODE,

AOD.EXPIRATION_ID_DATE,

AOD.PUT_CALL_INDICATOR,

AOD.STRIKE_PRICE,

CM.CONTRACT_MARKET_NAME,

CM.EXCHANGE_INITIALS

)

SELECT *

FROM (

SELECT

LT_DATA.REPORTING_FIRM_ID,

LT_DATA.CONTRACT_MARKET_CODE,

LT_DATA.CONTRACT_MARKET,

LT_DATA.FUTURES_EXPIRATION_DATE,

LT_DATA.OPTION_CLASS_CODE,

LT_DATA.EXPIRATION_ID_DATE AS 'OPTIONS_EXPIRATION_DATE',

LT_DATA.PUT_CALL_INDICATOR,

LT_DATA.STRIKE_PRICE,

LT_DATA.REPORT_DATE,

COALESCE(EX_DATA.LONG, 0) AS 'L_Exch',

COALESCE(OM_DATA.LONG, 0) AS L_CB,

COALESCE(LT_DATA.LONG, 0) AS L_LargeTrader,

COALESCE(EX_DATA.LONG, 0) + COALESCE(OM_DATA.LONG, 0) - COALESCE(LT_DATA.LONG, 0) AS L_Difference,

COALESCE(EX_DATA.SHORT, 0) AS 'S_Exch',

COALESCE(OM_DATA.SHORT, 0) AS S_CB,

COALESCE(LT_DATA.SHORT, 0) AS S_LargeTrader,

COALESCE(EX_DATA.SHORT, 0) + COALESCE(OM_DATA.SHORT, 0) - COALESCE(LT_DATA.SHORT, 0) AS S_Difference,

OptCrDt.LAST_PROCESSING_DATE,

REPORTING_FIRM_RESP_PER.SYSUSER_ID,

CM.CURRENT_RPT_LEVEL

FROM CTE_LT_DATA AS LT_DATA

INNER JOIN OPTIONS_CRITICAL_DATE AS OptCrDt WITH (NOLOCK)

ON LT_DATA.CONTRACT_MARKET_CODE = OptCrDt.CONTRACT_MARKET_CODE

AND LT_DATA.EXPIRATION_ID_DATE = OptCrDt.EXPIRATION_ID_DATE

AND LT_DATA.OPTION_CLASS_CODE = OptCrDt.OPTION_CLASS_CODE

AND LT_DATA.FUTURES_EXPIRATION_DATE = OptCrDt.FUTURES_EXPIRATION_DATE

INNER JOIN REPORTING_FIRM_RESP_PER WITH (NOLOCK)

ON LT_DATA.REPORTING_FIRM_ID = REPORTING_FIRM_RESP_PER.REPORTING_FIRM_ID

INNER JOIN CONTRACT_MARKET AS CM WITH (NOLOCK)

ON CM.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE

LEFT JOIN CTE_OM_DATA AS OM_DATA

ON OM_DATA.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE

AND OM_DATA.FUTURES_EXPIRATION_DATE = LT_DATA.FUTURES_EXPIRATION_DATE

AND OM_DATA.REPORT_DATE = LT_DATA.REPORT_DATE

AND OM_DATA.STRIKE_PRICE = LT_DATA.STRIKE_PRICE

AND OM_DATA.PUT_CALL_INDICATOR = LT_DATA.PUT_CALL_INDICATOR

AND OM_DATA.EXPIRATION_ID_DATE = LT_DATA.EXPIRATION_ID_DATE

AND OM_DATA.OPTION_CLASS_CODE = LT_DATA.OPTION_CLASS_CODE

AND OM_DATA.OMNI_RF = LT_DATA.REPORTING_FIRM_ID

LEFT JOIN CTE_EX_DATA AS EX_DATA

ON EX_DATA.CONTRACT_MARKET_CODE = LT_DATA.CONTRACT_MARKET_CODE

AND EX_DATA.FUTURES_EXPIRATION_DATE = LT_DATA.FUTURES_EXPIRATION_DATE

AND EX_DATA.REPORT_DATE = LT_DATA.REPORT_DATE

AND EX_DATA.STRIKE_PRICE = LT_DATA.STRIKE_PRICE

AND EX_DATA.PUT_CALL_INDICATOR = LT_DATA.PUT_CALL_INDICATOR

AND EX_DATA.EXPIRATION_ID_DATE = LT_DATA.EXPIRATION_ID_DATE

AND EX_DATA.OPTION_CLASS_CODE = LT_DATA.OPTION_CLASS_CODE

AND EX_DATA.REPORTING_FIRM_ID = LT_DATA.REPORTING_FIRM_ID

) AS Opt_00_01

WHERE

(L_Difference < CURRENT_RPT_LEVEL * -1 OR S_Difference < CURRENT_RPT_LEVEL * -1)

AND LAST_PROCESSING_DATE > REPORT_DATE

AND REPORT_DATE BETWEEN '2023-01-01' AND '2023-04-30'

AND SYSUSER_ID LIKE '%blindsey%';