Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Gift_Card Interview SQL 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
First python practice
pyFoodTipFoodOrder
Python, finditer(), filter emails and place put in tuple
shuru9
Code hello world
Random module
количество 1 и 0 в последовательности чисел до n
Guessing game
Help me
BiseccionPython