Create table DEMOGRAPHICS
(
userId varchar(10),
biologicalSex varchar(50),
age int
)
Create table MAIN
(
featureName varchar(50),
date date,
userId varchar(10),
launchAttempts int,
build varchar(50)
)
INSERT INTO DEMOGRAPHICS
VALUES('1111', 'F', 41 ),
('2222', 'F', 18 ),
('3333', 'M', 21 ),
('4444', 'Not Set', 13 ),
('5555', 'F', 19 ),
('6666', 'M', 33 ),
('7777', 'M', 45 ),
('8888', 'Not Set', 33 ),
('9999', 'F', 51 ),
('1010', 'Not Set', 22 ),
('1212', 'F', 19 ),
('1313', 'M', 44 ),
('1414', 'M', 40 ),
('1515', 'Not Set', 33 ),
('1616', 'F', 51 ),
('1717', 'Not Set', 22 )
go
INSERT INTO MAIN
VALUES('AAA','11-01-2020','1111',5,'18A'),
('BBB','01-02-2021','1111',0,'18B'),
('CCC','11-10-2020','2222',1,'18B'),
('DDD','12-15-2020','3333',2,'18C'),
('AAA','12-10-2020','4444',6,'18B'),
('BBB','12-22-2020','4444',2,'18B'),
('CCC','12-25-2020','5555',0,'18C'),
('DDD','12-05-2020','6666',4,'18A'),
('AAA','12-04-2020','7777',3,'18C'),
('BBB','12-06-2020','1010',2,'18C'),
('AAA','12-15-2020','8888',3,'18D'),
('BBB','12-10-2020','9999',2,'18C'),
('AAA','12-22-2020','6666',6,'18B'),
('BBB','12-22-2020','4444',2,'18B'),
('CCC','12-22-2020','5555',0,'18C'),
('DDD','07-15-2020','6666',4,'18A'),
('AAA','12-09-2020','1010',3,'18F'),
('AAA','12-03-2020','1313',6,'18B'),
('CCC','12-22-2020','1212',2,'18B'),
('CCC','12-09-2020','1313',0,'18A'),
('DDD','12-05-2020','1414',4,'18A'),
('AAA','12-09-2020','1515',3,'18E'),
('BBB','07-10-2020','1616',2,'18A'),
('AAA','12-15-2020','3333',3,'18F'),
('CCC','12-22-2020','1717',2,'18A')
GO
--1. Construct an SQL query to calculate Monthly Active Users (MAU) for each feature for Dec 2020
SELECT M.featurename,
Count(DISTINCT( M.userid )) AS MAU
FROM MAIN AS M
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
GROUP BY featurename
GO
--1.1 Only include users <45 years of age
SELECT M.featurename,
Count(DISTINCT( M.userid )) AS MAU
FROM MAIN AS M
INNER JOIN demographics AS D
ON M.userid = D.userid
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
AND D.age < 45
GROUP BY featurename
GO
--1.2 Only include build > 18A for feature AAA
GO
SELECT M.featurename,
Count(DISTINCT( M.userid )) AS MAU
FROM MAIN AS M
INNER JOIN demographics AS D
ON M.userid = D.userid
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
AND D.age < 45
AND build > '18A'
AND featurename = 'AAA'
GROUP BY featurename
GO
--2. Construct an SQL query to calculate Average Daily Active Users (DAU) for each feature for Dec 2020
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU,
featurename
FROM main AS M
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
GROUP BY date,featurename)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU, featurename
FROM DAU
group by featurename
GO
--2.1 Only include users <45 years of age
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU,
featurename
FROM main AS M
INNER JOIN demographics AS D
ON M.userid = D.userid
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
AND D.age < 45
GROUP BY date, featurename)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU, featurename
FROM DAU
group by featurename
GO
--2.2 Only include build > 18A for feature AAA
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU,
featurename
FROM main AS M
INNER JOIN demographics AS D
ON M.userid = D.userid
WHERE launchattempts > 0
AND M.date BETWEEN '12-01-2020' AND '12-31-2020'
AND build > '18A'
AND featurename = 'AAA'
GROUP BY date, featurename)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU, featurename
FROM DAU
group by featurename
Telegram: DB developers channel