SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear