SQLize Online / PHPize Online  /  SQLtest Online

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