特斯拉售后数据分析师
Q1:
Count male, female and total people of each city
CREATE TABLE test1 ([city]varchar(10),[gender]char(1))
INSERT INTO test1 ([city],[gender])
VALUES('Delhi','M')
,('Delhi','F')
途观撞rav4,('Delhi','M')
,('Delhi','M')
,('Pune','F')
,
租车价格明细表('Pune','M')
,('Pune','F')
衡阳中心汽车站,('Pune','F')
,('Banglore','F')
,('Banglore','F')
;
SELECT
city,
COUNT(CASE gender WHEN'M'THEN1ELSE0END)AS male_num,
COUNT(CASE gender WHEN'F'THEN1ELSE0END)AS female_num,
COUNT(gender)AS people_num
FROM test1
GROUP BY city
Q2:
You’re interviewing at a large social media company and are given two tables: registrations and logins. Each table consists of a user id and a date timestamp of the event. Each user has only one registration entry but could have multiple login entries (or none). The question is to write a query that will give the number of times each user login within their first week of registration (including none).
CREATE TABLE registration ([user_id]char(1),[registration_date]date)
INSERT INTO registration ([user_id],[registration_date])
VALUES('1','2020-01-15')
,('2','2020-02-01')
,('3','2020-03-10')
,
('4','2020-04-07')
,('5','2020-05-25')
;
CREATE TABLE logins ([user_id]char(1),[login_date]date)
INSERT INTO logins ([user_id],[login_date])
VALUES('1','2020-01-17')
,('1','2020-01-19')
,('1','2020-01-20')
,('1','2020-01-25')
,('2','2020-02-01')
,('2','2020-02-05')
,
('2','2020-02-08')
,('4','2020-04-08')
,('4','2020-04-09')
,('4','2020-04-10')
,('4','2020-04-15')
,('4','2020-04-20')
,('5','2020-05-31')
,('5','2020-06-05')
;
SELECT registration.user_id,
COUNT(login_date)AS login_num
FROM registration
LEFT JOIN logins
ON registration.user_id = logins.user_id
WHERE DATEDIFF(DAY,registration_date,login_date)<=7
GROUP BY registration.user_id
Q3
Write a SQL query to prepare a table with salesman name, customer name and their city, for the salesman and customer who belongs to the same city.
CREATE TABLE salesman ([salesman_id]char(4),
[name]varchar(20),
[city]varchar(20),
[commission]float(24))
福建车友会INSERT INTO salesman ([salesman_id],
[name],
[city],
[commission])
VALUES('5001','James Hoog','New York',0.15),
('6785','Nail Knite','Paris',0.13),
('2673','Pit Alex','London',0.11),
('8736','Mc Lyon','Paris',0.14),
('4783','Paul Adam','Rome',0.13),
('9937','Lauson Hen','San Jose',0.12)
索兰托2012;
CREATE TABLE customer ([customer_id]char(6),
[name]varchar(20),
[city]varchar(20),
[grade]int,
[salesman_id]char(4))
INSERT INTO customer ([customer_id],
[name],
[city],
[grade],
[salesman_id])
VALUES('123456','Nick Rimando','New York',100,'5001'),苏州客运总站网上订票
('263574','Brad Davis','New York',200,'5001'),
('736582','Graham Zusi','Los Angelas',200,'6785'),
('208957','Julian Green','London',300,'6785'),
('276893','Fabian Johnson','Paris',300,'8736'),
('678396','Geoff Cameron','Berlin',100,'9937'),
('290837','Jozy Altidor','Moscow',200,'4783')
;
SELECT
salesman.name AS salesmanname,
customer.name AS customername,
salesman.city
FROM salesman
JOIN customer
ON salesman.city = customer.city
发布评论