当前位置:网站首页>A written SQL test question

A written SQL test question

2021-09-15 07:24:15 Data Studio

Hello everyone , I am cloud king !

A data worker interviews data related positions ,SQL Query statement is an essential written test link , Today, yundojun brought you an interview question from a factory , Reference answers are attached , I hope I can help you !

Click to follow | Choose the star | Dry goods express

◎ Daily approval rate and average approved application amount ◎ 2018 year 2-5 month , Number of loans with different rates 、 Loan amount 、30 Overdue rate of amount over days ( Remaining principal / Loan amount ) ◎ Of all lenders , Proportion of different customer groups

The application form app_list

Field name : Application date , Contract No , Application amount , Approval result

apply_date

loan_no

apply_prin

result

2018/2/5

GM290114

10000

pass

2018/2/5

GM290140

10000

pass

2018/2/5

GM290144

10000

pass

2018/3/1

GM290923

10000

reject

2018/3/1

GM290937

10000

reject

2018/3/1

GM290938

10000

pass

2018/4/17

GM29571

8000

pass

Refer to the answer

※ Daily approval rate and average approved application amount

* analysis :

① Daily -- The application date needs to be apply_date polymerization group by

② Approval rate -- Calculate the total number of passes divided by the total number of applications . Judge result = 'pass' Then through , Equal is 1, If you don't wait, it's 0, Using summation function sum() The total number of passes can be calculated . The total number of applications can be counted directly count() that will do .

③ Average approved application amount -- Logic similar to Article 2 , Directly divide the passing amount by the total passing amount .

SELECT apply_date, 
       SUM(result = 'pass')/COUNT(loan_no)  Approval rate ,  --  Alias 
       SUM((result = 'pass' )*apply_prin)/SUM(result='pass')  Average approved application amount  --  Alias 
FROM app_list
GROUP BY apply_date;

* result :

apply_date

Approval rate

Average approved application amount

2018/2/5

1

10000

2018/3/1

0.3333

10000

2018/4/17

1

8000

2018/5/11

1

6000

2018/5/25

0.3333

15000

2018/6/18

1

1000

2018/10/12

1

12000

2018/11/5

0.6667

20000

Loan schedule loan_list

Field name : Disbursement date , Contract No , ID number , Loan amount , Principal paid , Consumption level , Expected days

loan_date

loan_no

id_no

loan_prin

paid_principal

product_rate

overdue_days

2018/2/5

GM290144

1100001990

10000

8000

A

NULL

2018/4/17

GM296833

5500001992

8000

1500

D

11

2018/5/11

GM310938

2300001991

6000

5500

D

NULL

2018/6/18

GM350939

4500001989

1000

0

B

432

2018/4/18

GM296834

5100001992

6000

1500

D

31

2018/4/20

GM296894

5100001982

60000

15000

D

40

2018/3/20

GM296874

5100001987

13000

10000

D

60

※ 2018 year 2-5 month , Number of loans with different rates 、 Loan amount 、30 Overdue rate of amount over days ( Remaining principal / Loan amount )

* analysis :

① 2018 year 2-5 month -- adopt where Filter it .

② Number of loans 、 Loan amount -- Use the count function separately count() Sum function sum() that will do .

③ 30 Overdue rate of amount over days ( Remaining principal / Loan amount )

  1. Within the time limit 30 Days or more -- overdue_days>=30
  2. Remaining principal -- Loan amount less principal repaid loan_prin - paid_principal
  3. Multiply and sum the above two , You can get Within the time limit 30 Remaining principal over days
  4. Find out by word list 2018 year 2-5 Total amount of loans in the month
select product_rate, 
			 count(loan_no)  Number of loans , 
			 sum(loan_prin)  Loan amount ,
			 ifnull(sum((loan_prin - paid_principal)*(overdue_days>=30))/ 
						  (select sum(loan_prin) 
               from loan_list 
               where month(loan_date) between 2 and 5  --  Time screening 
               and year(loan_date) = 2018),0) 30 Overdue rate of amount over days  
from loan_list
where month(loan_date) between 2 and 5 and year(loan_date) = 2018
group by product_rate;

* result

product_rate

Number of loans

Loan amount

30 Overdue rate of amount over days

A

1

10000

0

D

5

93000

0.5097

Customer information sheet customer

Field name : ID number , Customer group type , Age

id_no

groupp

age

1100001990

house

29

5500001992

creditcard

27

2300001991

creditcard

28

4500001989

creditcard

30

4500001988

house

31

5100001992

car

46

5100001982

car

35

5100001987

house

31

※ Of all lenders , Proportion of different customer groups

* analysis :

① Lending customer and customer group types belong to lending table and customer information table respectively , Therefore, table links are needed , Link field is ID number. id_no.

② Proportion of different people -- Lender customer de duplication count , Divided by the total number of all customers ( Query through word list )

SELECT groupp, 
			 COUNT(distinct loan_list.id_no)/
			 (SELECT count(distinct id_no) 
        FROM customer)  Proportion of people 
FROM loan_list left JOIN customer
ON loan_list.id_no = customer.id_no
GROUP BY groupp;

* result :

groupp

Proportion of people

car

0.25

creditcard

0.375

house

0.25

Table building and derivative

In order to facilitate the operation and contact of small partners , The database table creation and data import codes are posted to you .

-- create database STUDIO;
use STUDIO;

create table app_list
(apply_date date,
loan_no varchar(10) primary key,
apply_prin int,
result varchar(10));

insert into app_list values 
("2018-2-5","GM290144",10000,"pass"),
("2018-3-1","GM290937",10000,"reject"),
("2018-4-17","GM296833",8000,"pass"),
("2018-5-11","GM310938",6000,"pass"),
("2018-5-25","GM327400",15000,"reject"),
("2018-6-18","GM350939",1000,"pass"),
("2018-10-12","GM380936",12000,"pass"),
("2018-11-5","GM400940",20000,"reject"),
("2018-2-5","GM290140",10000,"pass"),
("2018-3-1","GM290938",10000,"pass"),
("2018-4-17","GM296843",8000,"pass"),
("2018-5-11","GM310939",6000,"pass"),
("2018-5-25","GM327401",15000,"pass"),
("2018-6-18","GM350966",1000,"pass"),
("2018-10-12","GM380976",12000,"pass"),
("2018-11-5","GM400949",20000,"pass"),
("2018-2-5","GM290114",10000,"pass"),
("2018-3-1","GM290923",10000,"reject"),
("2018-4-17","GM29571",8000,"pass"),
("2018-5-11","GM310928",6000,"pass"),
("2018-5-25","GM32411",15000,"reject"),
("2018-6-18","GM351939",1000,"pass"),
("2018-10-12","GM376936",12000,"pass"),
("2018-11-5","GM441940",20000,"pass");

select * from app_list;

create table loan_list
(loan_date date,
loan_no varchar(15),
id_no varchar(25),
loan_prin int,
paid_principal int,
product_rate varchar(2),
overdue_days int);

insert into loan_list values
("2018-2-5","GM290144","1100001990",10000,8000,"A",null),
("2018-4-17","GM296833","5500001992",8000,1500,"D",11),
("2018-5-11","GM310938","2300001991",6000,5500,"D",null),
("2018-6-18","GM350939","4500001989",1000,0,"B",432),
("2018-4-18","GM296834","5100001992",6000,1500,"D",31),
("2018-4-20","GM296894","5100001982",60000,15000,"D",40),
("2018-3-20","GM296874","5100001987",13000,10000,"D",60);

select * from loan_list;

create table customer(id_no varchar(25),
                      groupp varchar(25),
                      age int);
                      
insert into customer values
("1100001990","house",29),
("5500001992","creditcard",27),
("2300001991","creditcard",28),
("4500001989","creditcard",30),
("4500001988","house",31),
("5100001992","car",46),
("5100001982","car",35),
("5100001987","house",31);

select * from customer;

This article is from WeChat official account. - data STUDIO(jim_learning) , author : Cloud King

The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the yunjia_community@tencent.com Delete .

Original publication time : 2021-08-31

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[Data Studio]所创,转载请带上原文链接,感谢
https://chowdera.com/2021/09/20210909125824239c.html

随机推荐