Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
G.Giftcard Interview Analysis Conducted by Miranda Zhao
-- G.Giftcard Interview Analysis Conducted by Miranda Zhao ----------------------------------------------------------------------- -- database Script CREATE TABLE Giftcard_Table1 ("order_time" timestamp, "order_value" int, "order_id" int) ; INSERT INTO Giftcard_Table1 ("order_time" , "order_value" , "order_id") VALUES ('2019-02-01', 6, 12345), ('2019-03-05', 10, 67890), ('2019-04-03', 4, 23456), ('2019-03-05', 100, 67890); --#0: View Table select * from Giftcard_Table1; --#1: Count # of transactions in 2017 select count (order_id) as transaction_2017 from Giftcard_Table1 where order_time between '2017-01-01' and '2017-12-31'; --#2:Count # of transactions with order value<=$5, and # of transactions with order value >$5 in one query select count(distinct(case when order_value <=5 then order_id else 0 end)) as txns_lessthan5, count(distinct(case when order_value >5 then order_id else 0 end)) as txns_greaterthan5 from Giftcard_Table1; --#3: Find % of transactions <=$5, and >$5 with cte as (select count(distinct(case when order_value <=5 then order_id else 0 end)) as txns_lessthan5, count(distinct(case when order_value >5 then order_id else 0 end)) as txns_greaterthan5, count(distinct(order_id)) as total_order from Giftcard_Table1) select txns_lessthan5/ total_order as txnsper_lessthan5, txns_greaterthan5/ total_order as txnsper_greaterthan5 from cte; --#4. Find the days of table 1 with total order value >$100 With cte1 as( Select Order_time as Date, sum(order_value) as TotalValue From Giftcard_Table1 Group by Order_time) select * from cte1 Where TotalValue > 100; --5. Top 10 orders of each day by order value, only orders in 2019 With cte1 as( Select Order_time, Order_id, order_value, row_number() over(partition by Order_time order by order_value DESC) From Giftcard_Table1 Where Order_time between '1/1/19' and '12/31/2019' order by Order_time) Select Order_time, Order_id, order_value From cte1 Where row_number <=10;
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Finale8E9237394
arjun
Pedido_Insert_Into
test
SQL - Social Network Analysis by Patrick Lin
find null in any column of table using PostgreSQL
postgresql timestamp
ECommerce Website SQL Analysis by Iggy Zhao
sss
Branch
Please log in to post a comment.