replit sql challenge

replit_sql_challenge incomplete.txt

.header on
.mode column
PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  years_tenure REAL,
  state TEXT,
  clv REAL
);

INSERT INTO customers VALUES
  (1, 2.1, 'WI', 1000),
  (2, 0.5, 'MA', 100),
  (3, 3.21, 'WI', 1400);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  price REAL
);

INSERT INTO products VALUES
  (10001, 1.2),
  (10005, 50);

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  transaction_on TEXT,
  update_on TEXT,
  FOREIGN KEY(customer_id) REFERENCES customers(id),
  FOREIGN KEY(product_id) REFERENCES products(id)
);

INSERT INTO transactions VALUES
  (1, 3, 10001, 10, "2020-11-01 10:20:05.123", datetime("now")),
  (2, 2, 10005, 1, "2020-11-01 07:20:05.000", datetime("now")),
  (3, 1, 10001, 2, "2020-11-05 14:45:05.000", datetime("now")),
  (4, 3, 10005, 1, "2020-11-01 10:20:05.123", datetime("now")),
  (5, 3, 10005, 1, "2020-11-05 10:20:05.123", datetime("now"));

-- .print '(1) Total CLV of all customers'
-- -- Your answer here

-- .print '(2) Total revenue from product 10001'
-- -- Your answer here

-- .print '(3) Home states of customers who made a purchase on November 1, 2020'
-- -- Your answer here
  
.print '(4) Customers who made a transaction on 11/1 who did not also make a transaction on 11/5'
select transactions.customer_id from transactions 
LEFT JOIN (SELECT customer_id from transactions where DATE(transactions.transaction_on) = '2020-11-05') as unwanted ON unwanted.customer_id = transactions.customer_id
WHERE DATE(transactions.transaction_on) = '2020-11-01' and unwanted.customer_id is null;


.print '(5) If you used an IN statement in your answer to 4, do it with a JOIN and no IN statement (if you used a JOIN and no IN statement in question 4, do it with an IN statement)'
select customer_id from transactions 
WHERE transactions.customer_id NOT IN (SELECT customer_id from transactions WHERE DATE(transaction_on) = '2020-11-05') AND DATE(transactions.transaction_on) = '2020-11-01';



.print '(6) The cumulative sum of each product sold, by day; the output should contain three fields (in any order) with product id, date and cumulative amount sold up to that date. See the comment for illustration'
-- Product 1, Date 1, Quantity Sold Day 1
-- Product 1, Date 2, Quantity Day 1 + 2
-- Product 1, Date 3, Quantity Sold Day 1 + 2 + 3
-- …
-- Product 2, Date 1, Quantity Sold Day 1
-- …
-- Your answer here