特斯拉-数据分析师-SQL笔试题

特斯拉售后数据分析师

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')
  ,('Delhi', 'M')
  ,('Delhi', 'M')
  ,('Pune', 'F')
  ,('Pune', 'M')
  ,('Pune', 'F')
  ,('Pune', 'F')
  ,('Banglore', 'F')
  ,('Banglore', 'F')
;

SELECT
  city,
  COUNT(CASE gender WHEN 'M' THEN 1 ELSE 0 END) AS male_num,
  COUNT(CASE gender WHEN 'F' THEN 1 ELSE 0 END) 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)
;

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