Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
G.Giftcard Interview Analysis Conducted by Miranda Zhao
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
-- 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
edit mode
|
history
|
discussion
order_time
order_value
order_id
1
01.02.2019 00:00:00
6
12345
2
05.03.2019 00:00:00
10
67890
3
03.04.2019 00:00:00
4
23456
4
05.03.2019 00:00:00
100
67890
transaction_2017
1
0
txns_lessthan5
txns_greaterthan5
1
2
3
txnsper_lessthan5
txnsper_greaterthan5
1
0
1
date
totalvalue
1
05.03.2019 00:00:00
110
order_time
order_id
order_value
1
01.02.2019 00:00:00
12345
6
2
05.03.2019 00:00:00
67890
100
3
05.03.2019 00:00:00
67890
10
4
03.04.2019 00:00:00
23456
4