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', 40 ),
('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
INNER JOIN demographics AS D
ON M.userid = D.userid
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
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU
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'
GROUP BY date)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU
FROM DAU
GO
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU
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)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU
FROM DAU
GO
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU
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)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU
FROM DAU
GO
WITH DAU
AS (SELECT date,
Count(DISTINCT m.userid) AS DAU
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 date)
SELECT Avg(Cast(dau AS FLOAT)) AS AVGDAU
FROM DAU
Telegram: DB developers channel