SQLize Online / PHPize Online  /  SQLtest Online

CREATE SCHEMA dannys_diner; CREATE TABLE sales ( "customer_id" VARCHAR(1), "order_date" DATE, "product_id" INTEGER ); INSERT INTO sales ("customer_id", "order_date", "product_id") VALUES ('A', '2021-01-01', '1'), ('A', '2021-01-01', '2'), ('A', '2021-01-07', '2'), ('A', '2021-01-10', '3'), ('A', '2021-01-11', '3'), ('A', '2021-01-11', '3'), ('B', '2021-01-01', '2'), ('B', '2021-01-02', '2'), ('B', '2021-01-04', '1'), ('B', '2021-01-11', '1'), ('B', '2021-01-16', '3'), ('B', '2021-02-01', '3'), ('C', '2021-01-01', '3'), ('C', '2021-01-01', '3'), ('C', '2021-01-07', '3'); CREATE TABLE menu ( "product_id" INTEGER, "product_name" VARCHAR(5), "price" INTEGER ); INSERT INTO menu ("product_id", "product_name", "price") VALUES ('1', 'sushi', '10'), ('2', 'curry', '15'), ('3', 'ramen', '12'); CREATE TABLE members ( "customer_id" VARCHAR(1), "join_date" DATE ); INSERT INTO members ("customer_id", "join_date") VALUES ('A', '2021-01-07'), ('B', '2021-01-09'); SELECT * FROM dbo.members; SELECT * FROM dbo.menu; SELECT * FROM dbo.sales; SELECT s.customer_id, m.product_name, --- sub query selecting customer, product name, count of product id aliased, dense ranked customer_id ordering by count of customer id aliased as rank COUNT(m.product_id) AS order_count, DENSE_RANK() OVER(PARTITION BY s.customer_id ORDER BY COUNT(s.customer_id) DESC) AS rank FROM dbo.menu AS m JOIN dbo.sales AS s ON m.product_id = s.product_id GROUP BY s.customer_id, m.product_name