2023-03-21 23:01 北京邮电大学数据分析师发布于北京

关注

中国民生银行信用卡中心金融科技挑战赛数据算法赛道面经

TimeLine：线上初赛202208??，线下复赛20220822-20220826

当时的BG：北邮本硕，管理类专业，两段实习经历：字节数据分析师、美团商业分析师

写在前面的话：以下问题的答案必然存在错误，敬请读者批评指正

线上初赛

主要考察算法和SQL，算法的难度不是特别高，但SQL有一定难度

民生银行卡中心的代码平台仅有上传功能，并不是OJ，因此只能在本地进行样例测试

初试没有屏幕监控，因此理论上可求助于任何渠道（划掉）

算法题

题目描述：

输入格式：

输出格式：

input_nums = []
while True:
    inputing = int(input())
    if inputing == -1:
        break
    else:
        input_nums.append(inputing)

res = [0] * len(input_nums)

for index in range(len(input_nums)):
    for i in range(1, input_nums[index] + 1):
        res[index] += input_nums[index]//i * i
        
for num in res:
    print(num)

input_nums = []
while True:
    inputing = int(input())
    if inputing == -1:
        break
    else:
        input_nums.append(inputing)

res = [0] * len(input_nums)

for index in range(len(input_nums)):
    for i in range(1, input_nums[index] + 1):
        tmp = []
        for j in range(1, int(i ** 0.5) + 1):
            if i % j == 0:
                tmp.append(j)
                tmp.append(int(i/j))
        res[index] += sum(set(tmp))
        
for num in res:
    print(num)

题目描述：

输入格式：

输出格式：

def isprime(x):
    if x < 2:
        return False
    for i in range(2, int(x ** 0.5) + 1):
        if x % i == 0:
            return False
    return True

input_nums = []
while True:
    inputing = int(input())
    if inputing == -1:
        break
    else:
        input_nums.append(inputing)

res = [0] * len(input_nums)

for index in range(len(input_nums)):
    for i in range(1, input_nums[index] + 1):
        tmp = []
        if (int(str(i)) != int(str(i)[::-1])) and isprime(i) and isprime(int(str(i)[::-1])):
            tmp.append(i)
        res[index] += sum(tmp)

for num in res:
    print(num)

题目描述：

输入格式：

输出格式：

input_nums = []
while True:
    inputing = int(input())
    if inputing == -1:
        break
    else:
        input_nums.append(inputing)

res = [0] * len(input_nums)

for index in range(len(input_nums)):
    for i in range(1, input_nums[index] + 1):
        res[index] += str(i).count('2')

for num in res:
    print(num)

SQL题

注：SQL题在本地不好测试，因此以下代码仅提供思路上的参考

数据表详情如下：

数据表示例性字段如下：

问题1：请用sql查询语句，给出login_day中不同日期的种类个数以及最大日期和最小日期是什么？

select
count(login_day) as '日期种类',
max(login_day) as '最大日期',
min(login_day) as '最小日期'
from app_data
group by login_day

问题2：连登次数是指连续登录success且没有出现timeout或fail，login_day不需要连续。请写一段sql语句来计算每个客户最多的连登次数，结果以client_id升序排列

with a as
(
    select client_id,
    login_day,
    result,
    lead(client_id, 1) over(partition by client_id order by login_day) as next_result
    from app_data
)
select client_id,
count(result) as '次数'
from a
where result = 'success' and next_result = 'success'
group by client_id
order by client_id asc

问题3：请用sql语句来计算每个客户最多的连续非fai的次数（login_day不需连续），结果以client_id升序排列

with a as
(
    select client_id,
    login_day,
    result,
    lead(client_id, 1) over(partition by client_id order by login_day) as next_result
    from app_data
)
select client_id,
count(result) as '最多连续非fail次数'
from a
where result != 'fail' and next_result != 'fail'
group by client_id
order by client_id asc

问题4：连续登录是指login_day连续不间断。请写出一段sql语句来计算每个客户连续登录success大于等于4天的次数，结果以client_id升序排列

with a as
(
    select client_id,
    login_day,
    result,
    lead(client_id, 1) over(partition by client_id order by login_day) as next_result
    from app_data
)
select client_id,
count(result) as '次数'
from a
where result = 'success' and next_result = 'success'
group by client_id
having count(result) >= 4
order by client_id asc

数据表详情如下：

数据表示例性字段如下：

问题1：请用sql语句计算首次交易后第二天再次交易的用户的比率，四舍五入到小数点后两位

with a as
(
    select client_id, trans_day,
    row_number() over(partition by client_id order by trans_day asc) as rn,
    date_sub(trans_day, interval rn day) as sub
    from trans_data
    where rn in (1, 2)
)
select round(count(client_id) / (select count(client_id) from trans_data group by client_id), 2) as Ratio
from a
group by client_id
having count(distinct sub) = 1

问题2：请用sql语句查询每个客户最近一次交易的amount，将amount按降序排列并给出前20行

with a as
(
    select client_id,
    trans_day, amount,
    row_number() over(partition by client_id order by trans_day desc) as rn
    from trans_data
)
select client_id, trans_day as '最近交易日期', amount
from a
where rn = 1
order by amount desc
limit 20