G.Giftcard Interview Analysis Conducted by Miranda Zhao, PostgreSQL

Language: Layout:

-- 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;


absolute service time: 1,72 sec