SELECT
A.EP_NAME AS EXPERT_NAME
, SUM(COALESCE(NULLIF(COOP_SKILL_PAYMENT, '')::numeric, 0)) AS SKILL_PAYMENT_SUM
, SUM(COALESCE(NULLIF(COOP_BROKERAGE, '')::numeric, 0)) AS BROKERAGE_SUM
, TRUNC((SUM(COALESCE(NULLIF(COOP_BROKERAGE, '')::numeric, 0)) / 1.1) * 0.3, 4) AS INCENTIVE_SUM
, SUM(COALESCE(NULLIF(COOP_RATIO, '')::numeric, 0)) AS NEW_CONTRACT_CNT
, COUNT(*) AS TRADE_CNT
FROM TAB_IPM_EXPERT A
INNER JOIN TAB_IPM_TRADE_DISTRIBUTE_COOPERATE_EXPERT B ON A.USER_SEQ = B.USER_SEQ
INNER JOIN TAB_IPM_TRADE C ON B.TRADE_ID = C.TRADE_ID
WHERE
A.EP_DEL_FLAG = 'N'
AND SUBSTR(C.REG_DATE,1,4) >= '2025'
AND SUBSTR(C.REG_DATE,1,4) <= '2025'
GROUP BY A.EP_NAME
