风控研发中心
…
导数sql及说明
急用钱D30+逾期用户拆分、累计复购（新老用户坏账率拆分）
转至元数据结尾
由
chenjian10添加，由
chenjian10最后更新于2017-06-29 (
查看更改)
转至元数据起始
jira：
JFBI-13 - 【急用钱(风控)】急用钱D30+逾期用户拆分、累计复购（新老用户坏账率拆分） (
关闭)
开发人：陈剑10、张亮2
开发时间：2017.06.14
急用钱D30+逾期用户拆分、累计复购（新老用户坏账率拆分）
SELECT
l.start_time as '统计日',l.member_id as '用户id',l.capital as '复购金额',l.member_loan_times as '复购次数',l0.start_time AS '首购日期',l.code_text as '渠道',l0.capital AS '首购金额'
FROM
(
SELECT
l.id,l.start_time,l.member_id,l.capital,l.member_loan_times -1 as member_loan_times,c.code_value,c.code_text,
(SELECT id
FROM
fin_vca_pda_loan l2
WHERE
l2.account_id = l.account_id
AND l2.loan_type = l.loan_type
AND l2.id <= l.id
AND l2.del_flag = 0
ORDER BY id ASC
LIMIT 1)
AS first_loan_id
FROM EDW.fin_vca_pda_loan l
INNER JOIN EDW.fin_vca_pda_account a ON a.member_id = l.member_id
INNER JOIN EDW.fin_fds_user_info_rt u ON u.member_id = l.member_id
INNER JOIN RPT.dim_cfp_channel_code c ON c.code_value = u.channel_code
WHERE l.del_flag = 0
-- AND l.add_time >= DATE('2017-06-12') AND l.add_time < DATE('2017-06-13')
-- AND l.update_time >= DATE('2017-06-12') AND l.update_time < DATE('2017-06-13')
AND l.start_time >= DATE('2017-05-13') AND l.start_time < DATE('2017-06-13')
AND l.loan_type = 0 AND member_loan_times > 1
) l
INNER JOIN EDW.fin_vca_pda_loan l0 ON l0.id = l.first_loan_id
WHERE l0.del_flag = 0
order by l.id desc
转载于:https://www.cnblogs.com/shujuxiong/p/11257906.html
相关资源：数据结构—成绩单生成器
