Queries.sql

----- CREATE TABLE------

CREATE TABLE SUBSCRIPTIONS(
USER_ID INT PRIMARY KEY,
JOIN_DATE DATE,
CITY VARCHAR(20),
SUBSCRIPTION_PLAN VARCHAR(20),
MONTHLY_FEE INT,
MONTHS_ACTIVE INT,
DEVICE VARCHAR(20),
PAYMENT_TYPE VARCHAR(20)
);

----- INSERT VALUES INTO TABLE --------

INSERT INTO SUBSCRIPTIONS VALUES
(6001,'2026-01-05','DELHI','BASIC',199,3,'MOBILE','UPI'),
(6002,'2026-01-06','MUMBAI','PREMIUM',499,2,'TV','CARD'),
(6003,'2026-01-07','BANGALORE','STANDARD',299,4,'LAPTOP','UPI'),
(6004,'2026-01-08','DELHI','PREMIUM',499,1,'TV','CARD'),
(6005,'2026-01-09','HYDERABAD','BASIC',199,5,'MOBILE','UPI'),
(6006,'2026-01-10','MUMBAI','STANDARD',299,2,'LAPTOP','CARD'),
(6007,'2026-01-11','BANGALORE','PREMIUM',499,3,'TV','UPI'),
(6008,'2026-01-12','HYDERABAD','BASIC',199,4,'MOBILE','UPI'),
(6009,'2026-01-13','DELHI','STANDARD',299,2,'LAPTOP','CARD'),
(6010,'2026-01-14','MUMBAI','PREMIUM',499,6,'TV','CARD')

----- SEE WHOLE DATA ------

SELECT * FROM SUBSCRIPTIONS;

----- TOTAL REVENUE ------

SELECT
  SUM(MONTHLY_FEE*MONTHS_ACTIVE) AS TOTAL_REVENUE
FROM SUBSCRIPTIONS;

----- REVENUE BY PLAN ------

SELECT
  SUBSCRIPTION_PLAN,
  SUM(MONTHLY_FEE*MONTHS_ACTIVE) AS TOTAL_REVENUE
FROM SUBSCRIPTIONS
GROUP BY SUBSCRIPTION_PLAN
ORDER BY TOTAL_REVENUE DESC;

----- AVERAGE RETENTION -----

SELECT
  SUBSCRIPTION_PLAN,
  SUM(MONTHS_ACTIVE) AS AVG_MONTHS
FROM SUBSCRIPTIONS
GROUP BY SUBSCRIPTION_PLAN;

----- USERS BY DEVICE ------

SELECT 
  DEVICE,
  COUNT(*) AS USERS
  FROM SUBSCRIPTIONS
GROUP BY DEVICE;

----- REVENUE BY DEVICE -----

SELECT
  DEVICE,
  SUM(MONTHLY_FEE*MONTHS_ACTIVE) AS TOTAL_REVENUE
FROM SUBSCRIPTIONS
GROUP BY DEVICE
ORDER BY TOTAL_REVENUE DESC;

----- REVENUE BY CITY -----

SELECT
  CITY,
  SUM(MONTHLY_FEE*MONTHS_ACTIVE) AS TOTAL_REVENUE
FROM SUBSCRIPTIONS
GROUP BY CITY
ORDER BY TOTAL_REVENUE DESC;

----- REVENUE BY PAYMENT_TYPE -----

SELECT
  PAYMENT_TYPE,
  SUM(MONTHLY_FEE*MONTHS_ACTIVE) AS TOTAL_REVENUE
FROM SUBSCRIPTIONS
GROUP BY PAYMENT_TYPE
ORDER BY TOTAL_REVENUE DESC;