SELECT rownum 序号,
USR.USER_ID AS 用户编码,
USR.USR_REG_DT AS 注册日期,
CRE.AC_CRE_DT_MAX AS 最近获得额度时间, --授信日期（若未成功获得授信额度则为空，如拒贷也为空）
BILL.BILL_REG_DT_MAX AS 最近借款日期, --借款日期(指实际签约日期。若未借款则为空)
C.NODE_REMARK 节点名称,
A.COUP_NM 优惠券名称, --（这里是后台配置的优惠券名称）
CASE
WHEN B.COUP_CATEGORY = '00' THEN
'面值' || b.COUP_VALUE
WHEN B.COUP_CATEGORY = '01' THEN
'折扣' || b.COUP_VALUE
WHEN B.COUP_CATEGORY = '02' THEN
'天数' || b.COUP_VALUE
WHEN B.COUP_CATEGORY = '03' THEN
'免息/费' || b.COUP_VALUE
END AS 优惠券种类,
CASE
WHEN A.COUP_TYP = '00' THEN
'抵息券'
WHEN A.COUP_TYP = '01' THEN
'信用审核费券'
WHEN A.COUP_TYP = '02' THEN
'分期服务费券'
WHEN A.COUP_TYP = '03' THEN
'逾期费券'
END AS 优惠券类型,
CASE
WHEN A.USE_STS = '00' THEN
'未使用'
WHEN A.USE_STS = '01' THEN
'已锁定'
WHEN A.USE_STS = '02' THEN
'已使用'
WHEN A.USE_STS = '03' THEN
'已作废'
END AS 使用状态,
CASE
WHEN B.USE_SCENE = '00' THEN
'借款'
WHEN B.USE_SCENE = '01' THEN
'还款'
END AS 使用节点,
TO_CHAR(A.DEL_TM, 'yyyymmdd') 发放时间,
T.RECORD_DT 使用时间,
T.BILL_NO 使用账单号,
TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') AS 使用天数,
case
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 3 then
'3天内'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 7 then
'一周内'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') >7 and
TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 14 then
'1-2周'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') > 14 and
TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 29 then
'2-4周'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') >= 30 then
'一个月以上'
end as 使用周期,
T.DEDUCTION_AMT AS 实际抵扣金额,
CASE
WHEN A.PROD_CODE = '01' THEN
'还到'
WHEN A.PROD_CODE = '02' THEN
'钱包'
WHEN A.PROD_CODE = '03' THEN
'商户贷'
END AS 产品类型
FROM FIN_BASE.T_DIM_USR USR
LEFT JOIN FIN_BASE.T_DIM_ACCOUNT_CREDIT CRE
ON CRE.USER_ID = USR.USER_ID
AND USR.BUSI_TYPE = CRE.BUSI_TYPE
LEFT JOIN FIN_BASE.T_GATHER_USER_BILL BILL
ON BILL.USER_ID = USR.USER_ID
AND BILL.PRD_TYP = USR.BUSI_TYPE
LEFT JOIN FIN_BASE.T_CPS_COUP_USR_IF A --用户优惠券表
ON A.USR_ID = USR.USER_ID
AND USR.BUSI_TYPE = A.PROD_CODE
LEFT JOIN FIN_BASE.T_CPS_COUP_IF B --优惠券信息表
ON A.COUP_NO = B.COUP_NO
LEFT JOIN FIN_BASE.T_CPS_ACTIVITY_IF D --活动信息表
ON A.ACT_ID = D.ACTIVITY_ID
LEFT JOIN FIN_BASE.T_CPS_BUS_NODE_IF C --业务节点信息表
ON D.BUS_NODE = C.NODE_CODE
LEFT JOIN FIN_BASE.T_BLM_COUP_RECORD T
ON T.COUP_ID = A.COUP_ID
where A.DEL_TM IS NOT NULL
--使用天数
AND case
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 3 then
'3天内'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 7 then
'一周内'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') >7 and
TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 14 then
'1-2周'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') > 14 and
TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') <= 29 then
'2-4周'
when TO_DATE(T.RECORD_DT, 'yyyymmdd') -
TO_DATE(TO_CHAR(A.DEL_TM, 'yyyymmdd'), 'yyyymmdd') >= 30 then
'一个月以上'
end ='${参数名}'
1min目标场景问卷
立即参与
