即时分析师认证考试题

-- gp考试，执行角色选择：RCC组，集群选择：etron_exam，数据库选择：postgres
-- 如果是测试sql，为了速度，可以使用limit限制返回的数据量。

-- --用户表:
-- create table public.user_info(
-- user_id bigint,--用户编号
-- user_name text,--用户名
-- user_info jsonb,--用户详细信息
-- --参考： {user_id:"用户编号",user_name:"用户名",province:"籍贯省",city:"籍贯市",area:"籍贯区",user_info1:"",user_info2:"",...,user_info10:""}
-- register_time timestamp--注册时间
-- ) Distributed randomly;
-- 表数据量： 6kw

-- create table public.loan_record( --一次贷款一条记录
-- user_id text, --用户编号
-- loan_info json, --贷款详细信息 (15个key值) user_id,loan_amount, ...
-- --参考： {user_id:"用户编号",loan_amount:"贷款金额",user_info1:"",user_info2:"",...,user_info13:""}
-- loan_state char(1), --贷款状态，1 代表已经结清，0 代表未结清
-- loan_time timestamp
-- ) Distributed randomly;
-- 表数据量： 3亿

-- --用户所属公司信息：
-- create table public.company_info(
-- user_id bigint,--用户编号
-- company_name text,--公司名
-- legal_person text--法人
-- ) Distributed randomly;
-- 表数据量： 6kw

-- 题目一：查询广东省深圳市南山区 2020-12 月注册的所有用户的详细信息。返回列：user_id , user_name , user_info1 , user_info2 , user_info3。

--查询结果示例：
--       user_id       |   user_name   |            user_info1            |            user_info2            |            user_info3
-- --------------------+---------------+----------------------------------+----------------------------------+----------------------------------
--  111111111114002254 | user_14002254 | 5730c446ca70010725cf65a906821341 | 34da8d6e919259e6e5f99a87c0f1e2c0 | c7a6419db111f250ac78e27fbc2204b7
--  111111111114005673 | user_14005673 | df4bd6167f9354b916abf9cfbf44a5b5 | 420d5daf3d3528c3d0422186c014615e | 74c76bb0c17c1907e21a8a9677e4294a
--  111111111114009376 | user_14009376 | 24ed42adcef06d8d0e7be9a4baf98d9e | 88c26a364fb77736eab8e57d995d7cc6 | 79e21f7d89861afdb76a2c06159201e9
--  ...
select
    user_id,
    user_name,
    user_info->>'user_info1' as user_info1,
    user_info->>'user_info2' as user_info2,
    user_info->>'user_info3' as user_info3
from public.user_info
where register_time>='2020-12-01'
and register_time<'2021-01-01'
and user_info->>'province'='广东省'
and user_info->>'city'='深圳市'
and user_info->>'area'='南山区';

-- 题目二：查询 2019-01-01 日 注册的用户 每人已经结清的贷款次数。返回列：user_id , user_name , loan_clear_count(结清的贷款次数)。

--查询结果示例：
--       user_id       |   user_name   | loan_count
-- --------------------+---------------+------------
--  111111111159333058 | user_59333058 |          1
--  111111111116322448 | user_16322448 |          1
--  111111111114360348 | user_14360348 |          4
--  ...
select
    u.user_id,
    u.user_name,
    case when loan.loan_count is null then 0 else loan.loan_count end loan_count
from (
    select
        user_id::text,
        user_name
    from public.user_info
    where register_time>='2019-01-01'
    and register_time<'2019-01-02'
) u left join (
    select
        user_id,
        count(1) loan_count
    from public.loan_record
    where loan_state='1'
    and user_id in (select user_id::text from public.user_info where register_time>='2019-01-01' and register_time<'2019-01-02')
    group by user_id
) loan
on u.user_id=loan.user_id;

-- 题目三：查询 2020-01-01 日注册的所有用户 已经结清的贷款次数和未结清的贷款次数。返回列：user_id , user_name , loan_clear_count(已经结清的贷款次数) , loan_unclear_count(未结清的贷款次数)。

--查询结果示例：
--       user_id       |   user_name   | loan_clear_count | loan_unclear_count
-- --------------------+---------------+------------------+--------------------
--  111111111145593800 | user_45593800 |                5 |                  4
--  111111111155817042 | user_55817042 |                1 |                  0
--  111111111120992990 | user_20992990 |                0 |                  1
--  ...
select
    u.user_id,
    u.user_name,
    case when loan.loan_clear_count is null then 0 else loan.loan_clear_count end loan_clear_count,
    case when loan.loan_unclear_count is null then 0 else loan.loan_unclear_count end loan_unclear_count
from (
    select
        user_id::text,
        user_name
    from public.user_info
    where register_time>='2020-01-01'
    and register_time<'2020-01-02'
) u left join (
    select
        user_id,
        sum(case loan_state when '1' then 1 else 0 end) loan_clear_count,
        sum(case loan_state when '0' then 1 else 0 end) loan_unclear_count
    from public.loan_record where user_id in (select user_id::text from public.user_info where register_time>='2020-01-01' and register_time<'2020-01-02')
    group by user_id
) loan
on u.user_id=loan.user_id;

-- 题目四：查询 2020-01月 注册的所有用户的归属公司信息 及贷款总额。返回列：user_id , company_name , loan_amount_total(贷款总额)。

--查询结果示例：
--       user_id       |           company_name           | loan_amount_total
-- --------------------+----------------------------------+-------------------
--  111111111126944305 | 693aa04d6899375dd7b584daf056a89b |             86293
--  111111111142438091 | 779878b617fdd1a396e1aba24f8c0966 |            120028
--  111111111137720328 | ad25dec616cebcff1a61b5fcaa97e6ef |            311601
--  ...
select
    u.user_id,
    c.company_name,
    case when loan.loan_amount_total is null then 0 else loan.loan_amount_total end loan_amount_total
from (select user_id::text uid_text,user_id,user_name from public.user_info where register_time>='2020-01-01' and register_time<'2020-02-01') u
left join public.company_info c on u.user_id=c.user_id
left join (
    select
        user_id,
        sum((loan_info->>'loan_amount')::bigint) loan_amount_total
    from public.loan_record where user_id in (select user_id::text from public.user_info where register_time>='2020-01-01' and register_time<'2020-02-01')
    group by user_id
) loan on u.uid_text=loan.user_id;

-- 题目五：查询 2020-06 月注册的用户 的最新一次贷款 且该次贷款金额大于等于该用户平均贷款金额  的贷款详细信息。 返回列：user_id , user_name , loan_amount , loan_info1 , loan_info2 , loan_info3 (loan_info1 , loan_info2 , loan_info3 为 loan_info 字段json中的3个key值)。
-- 不满足条件的用户不展示
--查询结果示例：
--       user_id       |   user_name   | loan_amount |            loan_info1            |            loan_info2            |            loan_info3
-- --------------------+---------------+-------------+----------------------------------+----------------------------------+----------------------------------
--      11111111112084 | user_2084     |       93924 | 7098f79f7bd94ee143e5c8ed0f0943ae | 701a16d3cdb8047c2c77fafaea35ac15 | 22959947c8f5f0df03742b2940aa8f0e
--      11111111114401 | user_4401     |       99716 | f4c92cc6404293a92686a5b37945b927 | 896274dd3947ca6bec38a61e12896b76 | 3a648912d83139f2ac43ba5f41e85d0d
--      11111111119807 | user_9807     |       62579 | d86219f1dc8ca4aed5fb97eea420a121 | b9fb037244442fbb6abc6220fe9f565a | 701f49d57741f81c719f66435786cb5d
--      ...
select
    user_id,
    user_name,
    loan_info->>'loan_amount' as loan_amount,
    loan_info->>'loan_info1' as loan_info1,
    loan_info->>'loan_info2' as loan_info2,
    loan_info->>'loan_info3' as loan_info3
from (
    select
        u.user_id,
        u.user_name,
        loan.loan_info,
        row_number() over (partition by loan.user_id order by loan.loan_time desc) as rn,
        avg((loan_info->>'loan_amount')::bigint) over (partition by loan.user_id) as avg_amount
    from (
        select
            user_id::text,
            user_name
        from public.user_info
        where register_time>='2020-06-01' and register_time<'2020-07-01'
    ) u join public.loan_record loan on u.user_id=loan.user_id
) tmp where rn = 1 and (loan_info->>'loan_amount')::bigint>=avg_amount;