Created
June 13, 2023 15:26
-
-
Save tonio-m/28841302bea3d64f782ccf4e8933c162 to your computer and use it in GitHub Desktop.
replit sql challenge
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
.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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment