Informatica Solutions: SQL Test : Sample Questions from Amazon Test (1/2)

Some companies are conducting SQL tests. Below are some sample questions from SQL test. These questions are asked in Amazon SQL test.

Question 1 / 10

Order Sample Data:

ORDER_DAY ORDER_ID PRODUCT_ID QUANTITY PRICE

------------------ -------------------- ---------- ---------- ----------

01-JUL-11 O1 P1 5 5

01-JUL-11 O2 P2 2 10

01-JUL-11 O3 P3 10 25

01-JUL-11 O4 P1 20 5

02-JUL-11 O5 P3 5 25

02-JUL-11 O6 P4 6 20

02-JUL-11 O7 P1 2 5

02-JUL-11 O8 P5 1 50

02-JUL-11 O9 P6 2 50

02-JUL-11 O10 P2 4 10

Get me all products that got sold both the days and the number of times the product is sold. Desired output

PRODUCT_ID COUNT

P1 3

P2 2

P3 2

The following table has been created for you

CREATE TABLE ORDERS

(

ORDER_DAY DATE,

ORDER_ID VARCHAR2 (20),

PRODUCT_ID VARCHAR2 (10),

QUANTITY NUMBER,

PRICE NUMBER

);

Answer:

select PRODUCT_ID,count(1) from orders

group by PRODUCT_ID

having count(1)>1

Question 2 / 10

Order Sample data:

ORDER_DAY ORDER_ID PRODUCT_ID QUANTITY PRICE

------------------ -------------------- ---------- ---------- ----------

01-JUL-11 O1 P1 5 5

01-JUL-11 O2 P2 2 10

01-JUL-11 O3 P3 10 25

01-JUL-11 O4 P1 20 5

02-JUL-11 O5 P3 5 25

02-JUL-11 O6 P4 6 20

02-JUL-11 O7 P1 2 5

02-JUL-11 O8 P5 1 50

02-JUL-11 O9 P6 2 50

02-JUL-11 O10 P2 4 10

Get me products that was ordered on 02-Jul-11 but not on 01-Jul-11
desired output

Answer:

select PRODUCT_ID from orders where ORDER_DAY = '02-JUL-11'

MINUS

select PRODUCT_ID from orders where ORDER_DAY = '01-JUL-11'

Question 3 / 10

Order Sample data:

ORDER_DAY ORDER_ID PRODUCT_ID QUANTITY PRICE

------------------ -------------------- ---------- ---------- ----------

01-JUL-11 O1 P1 5 5

01-JUL-11 O2 P2 2 10

01-JUL-11 O3 P3 10 25

01-JUL-11 O4 P1 20 5

02-JUL-11 O5 P3 5 25

02-JUL-11 O6 P4 6 20

02-JUL-11 O7 P1 2 5

02-JUL-11 O8 P5 1 50

02-JUL-11 O9 P6 2 50

02-JUL-11 O10 P2 4 10

Get me highest sold Products (Qty* Price) on both days , desired output

DATE PRODUCT_ID SOLD_AMOUNT

01-JUL-11 P3 250

02-JUL-11 P3 125

Answer:

select q1.ORDER_DAY AS DATE1,q1.PRODUCT_ID,q1.QUANTITY*q1.PRICE AS SOLD_AMOUNT from orders q1 ,

(select PRODUCT_ID,sum(QUANTITY*PRICE) high_sold from orders group by PRODUCT_ID) q2,

(select max(high_sold) as max_sold from

(

select PRODUCT_ID,sum(QUANTITY*PRICE) high_sold from orders group by PRODUCT_ID

) )

where q2.high_sold=q3.max_sold and q1.PRODUCT_ID=q2.PRODUCT_ID

Question 4 / 10

Order Sample data:

ORDER_DAY ORDER_ID PRODUCT_ID QUANTITY PRICE

------------------ -------------------- ---------- ---------- ----------

01-JUL-11 O1 P1 5 5

01-JUL-11 O2 P2 2 10

01-JUL-11 O3 P3 10 25

01-JUL-11 O4 P1 20 5

02-JUL-11 O5 P3 5 25

02-JUL-11 O6 P4 6 20

02-JUL-11 O7 P1 2 5

02-JUL-11 O8 P5 1 50

02-JUL-11 O9 P6 2 50

02-JUL-11 O10 P2 4 10

Get me for all products, total sales on 01-JUL-11 and 02-JUL-11 adjacent to each other

Desired Output:

P1 125 10

P2 20 40

P3 250 125

P4 0 120

P5 0 50

P6 0 100

Answer:

select PRODUCT_ID,nvl(max(decode(ORDER_DAY,'01-JUL-11',total_sales)),0) AS JULY1st_sales ,

nvl(max(decode(ORDER_DAY,'02-JUL-11',total_sales)),0) AS JULY2nd_sales

from

(

select PRODUCT_ID,ORDER_DAY,sum(QUANTITY*PRICE) total_sales

from orders group by PRODUCT_ID,ORDER_DAY

) X

group by PRODUCT_ID

order by PRODUCT_ID

Question 5 / 10

Order Sample data:

ORDER_DAY ORDER_ID PRODUCT_ID QUANTITY PRICE

------------------ -------------------- ---------- ---------- ----------

01-JUL-11 O1 P1 5 5

01-JUL-11 O2 P2 2 10

01-JUL-11 O3 P3 10 25

01-JUL-11 O4 P1 20 5

02-JUL-11 O5 P3 5 25

02-JUL-11 O6 P4 6 20

02-JUL-11 O7 P1 2 5

02-JUL-11 O8 P5 1 50

02-JUL-11 O9 P6 2 50

02-JUL-11 O10 P2 4 10

Get me all products daywise, that was ordered more than once
Desired output

01-JUL-11 P1

Answer:

select PRODUCT_ID,ORDER_DAY,count(1)

from orders group by PRODUCT_ID,ORDER_DAY

having count(1)>1

Question 6 / 10

Order Table:

Order Id	Item	Qty
O1	A1	5
O2	A2	1
O3	A3	3

Please provide SQL which will explode the above data into single unit level records as shown below

Desired Output:

Order Id	Item	Qty
O1	A1	1
O1	A1	1
O1	A1	1
O1	A1	1
O1	A1	1
O2	A2	1
O3	A3	1
O3	A3	1
O3	A3	1

The following table has been created for you

CREATE TABLE SIMPLE_ORDERS(

ORDER_ID VARCHAR2(40)

, ITEM VARCHAR2(10)

, QUANTITY NUMBER

);

Answer:

with counter(val) as

(select 1 from dual union

select 2 from dual union

select 3 from dual union

select 4 from dual union

select 5 from dual )

select S.ORDER_ID,S.ITEM, 1 from SIMPLE_ORDERS S ,

counter O

where

O.val<=S.QUANTITY

Question 7 / 10

Product

Sales_fact

SNAPSHOT_DAY PRODUCT_ID SALES_AMT

20-JUL-11 P1 10
20-JUL-11 P2 5
20-JUL-11 P8 100
20-JUL-11 P3 5
20-JUL-11 P4 25
20-JUL-11 P5 15
20-JUL-11 P6 35
20-JUL-11 P7 5
20-JUL-11 P9 30
20-JUL-11 P10 8
20-JUL-11 P11 45

Glance_view_fact

SNAPSHOT_DAY PRODUCT_ID GLANCE_VIEWS

20-JUL-11 P1 1000
20-JUL-11 P2 800
20-JUL-11 P8 700
20-JUL-11 P3 800
20-JUL-11 P4 500
20-JUL-11 P5 250
20-JUL-11 P6 10
20-JUL-11 P7 1000
20-JUL-11 P9 1500
20-JUL-11 P10 600
20-JUL-11 P12 670
20-JUL-11 P13 300
20-JUL-11 P14 230

Inventory_fact

SNAPSHOT_DAY PRODUCT_ID ON_HAND_QUANTITY

20-JUL-11 P1 100
20-JUL-11 P2 70
20-JUL-11 P8 90
20-JUL-11 P3 10
20-JUL-11 P4 30
20-JUL-11 P5 100
20-JUL-11 P6 120
20-JUL-11 P7 70
20-JUL-11 P9 90

Ad_spend_fact

SNAPSHOT_DAY PRODUCT_ID AD_SPENT

20-JUL-11 P1 10
20-JUL-11 P2 5
20-JUL-11 P8 100
20-JUL-11 P3 5
20-JUL-11 P4 25
20-JUL-11 P5 15
20-JUL-11 P6 35
20-JUL-11 P7 5
20-JUL-11 P9 30
20-JUL-11 P10 8
20-JUL-11 P11 45

Write a SQL that will give the top product by sales in each of the product groups and additionally gather GV, Inventory, and ad spend measures also for the product.

Output Required:

Book P1 10 1000 100 0
Electronics P4 25 500 30 30
Shoes P11 45 0 0 0
Video DVD P8 100 700 90 0

Answer:

select * from

(

select P.PRODUCT_ID,P.PRODUCT_GROUP,S.SALES_AMT , rank() over(partition by P.PRODUCT_GROUP ORDER BY S.SALES_AMT DESC) AS RNK ,

nvl(gv.GLANCE_VIEWS,0),nvl(i.ON_HAND_QUANTITY,0) ,nvl(a.AD_SPENT,0)

from Product P inner join Sales_fact S on P.PRODUCT_ID=S.PRODUCT_ID

LEFT OUTER JOIN Glance_view_fact gv on P.PRODUCT_ID=gv.PRODUCT_ID

LEFT OUTER JOIN Inventory_fact i on P.PRODUCT_ID=i.PRODUCT_ID

LEFT OUTER JOIN Ad_spend_fact a on P.PRODUCT_ID=a.PRODUCT_ID

) XX WHERE RNK=1

Question 8 / 10

Product

+------------+---------------+----------------+

| PRODUCT_ID | PRODUCT_GROUP | PRODUCT_NAME |

+------------+---------------+----------------+

| P1 | Book | Harry Potter 1 |

| P2 | Book | Harry Potter 2 |

| P3 | Electronics | Nikon 10 MPS |

| P4 | Electronics | Cannon 8 MPS |

| P5 | Electronics | Cannon 10 MPS |

| P6 | Video DVD | Pirates 1 |

| P7 | Video DVD | Pirates 2 |

| P8 | Video DVD | HP 1 |

| P9 | Video DVD | HP 2 |

| P10 | Shoes | Nike 10 |

| P11 | Shoes | Nike 11 |

| P12 | Shoes | Adidas 10 |

| P13 | Shoes | Adidas 09 |

| P14 | Book | God Father 1 |

| P15 | Book | God Father 2 |

+------------+---------------+----------------+

Sales_fact

SNAPSHOT_DAY PRODUCT_ID SALES_AMT

------------------ ---------- ----------

20-JUL-11 P1 10

20-JUL-11 P2 5

20-JUL-11 P8 100

20-JUL-11 P3 5

20-JUL-11 P4 25

20-JUL-11 P5 15

20-JUL-11 P6 35

20-JUL-11 P7 5

20-JUL-11 P9 30

20-JUL-11 P10 8

20-JUL-11 P11 45

Glance_view_fact

SNAPSHOT_DAY PRODUCT_ID GLANCE_VIEWS

------------------ ---------- ------------

20-JUL-11 P1 1000

20-JUL-11 P2 800

20-JUL-11 P8 700

20-JUL-11 P3 800

20-JUL-11 P4 500

20-JUL-11 P5 250

20-JUL-11 P6 10

20-JUL-11 P7 1000

20-JUL-11 P9 1500

20-JUL-11 P10 600

20-JUL-11 P12 670

20-JUL-11 P13 300

20-JUL-11 P14 230

Write a SQL to give all Products that have glance views but no sales.

Output Required:

P12

P13

P14

Answer:

select PRODUCT_ID from Glance_view_fact where PRODUCT_ID not in (

select PRODUCT_ID from Sales_fact)

Question 9 / 10

Product

+------------+---------------+----------------+

| PRODUCT_ID | PRODUCT_GROUP | PRODUCT_NAME |

+------------+---------------+----------------+

| P1 | Book | Harry Potter 1 |

| P2 | Book | Harry Potter 2 |

| P3 | Electronics | Nikon 10 MPS |

| P4 | Electronics | Cannon 8 MPS |

| P5 | Electronics | Cannon 10 MPS |

| P6 | Video DVD | Pirates 1 |

| P7 | Video DVD | Pirates 2 |

| P8 | Video DVD | HP 1 |

| P9 | Video DVD | HP 2 |

| P10 | Shoes | Nike 10 |

| P11 | Shoes | Nike 11 |

| P12 | Shoes | Adidas 10 |

| P13 | Shoes | Adidas 09 |

| P14 | Book | God Father 1 |

| P15 | Book | God Father 2 |

+------------+---------------+----------------+

Sales_fact

SNAPSHOT_DAY PRODUCT_ID SALES_AMT

------------------ ---------- ----------

20-JUL-11 P1 10

20-JUL-11 P2 5

20-JUL-11 P8 100

20-JUL-11 P3 5

20-JUL-11 P4 25

20-JUL-11 P5 15

20-JUL-11 P6 35

20-JUL-11 P7 5

20-JUL-11 P9 30

20-JUL-11 P10 8

20-JUL-11 P11 45

Write a SQL to give the sales of Electronics as a Percentage of Books

Output Required: 300

Answer:

SELECT E_SALES/B_SALES*100 FROM

(

select P.PRODUCT_GROUP,sum(S.SALES_AMT) AS E_SALES

from Product P inner join Sales_fact S on P.PRODUCT_ID=S.PRODUCT_ID

where P.PRODUCT_GROUP = 'Electronics'

group by P.PRODUCT_GROUP

) E,

(select P.PRODUCT_GROUP,sum(S.SALES_AMT) AS B_SALES

from Product P inner join Sales_fact S on P.PRODUCT_ID=S.PRODUCT_ID

where P.PRODUCT_GROUP = 'Book'

group by P.PRODUCT_GROUP

) B

where 1=1

Question 10 / 10

See a Phone Log table as below. It records all phone numbers that we dial in a given day.

Table name is PHONE_LOG

SOURCE_PHONE_NUMBER DESTINATION_PHONE_NUMBER CALL_START_DATETIME

------------------- ------------------------ ----------------

1234 4567 01/07/2011 10:00

1234 2345 01/07/2011 11:00

1234 3456 01/07/2011 12:00

1234 3456 01/07/2011 13:00

1234 4567 01/07/2011 15:00

1222 7890 01/07/2011 10:00

1222 7680 01/07/2011 12:00

1222 2345 01/07/2011 13:00

Please provide an SQL query to display the source_phone_number and a flag where the flag needs to be set to Y if first called number and last called number are the same and N if the first called number and last called number are different.

For the above input data, desired output should be

Source Number Is_Match
1222 N
1234 Y

The following table is created for you

CREATE TABLE PHONE_LOG

(SOURCE_PHONE_NUMBER NUMBER

, DESTINATION_PHONE_NUMBER NUMBER

, CALL_START_DATETIME DATE

);

Answer:

SELECT DISTINCT

SOURCE_PHONE_NUMBER,

NVL2 (

NULLIF (

(SELECT DESTINATION_PHONE_NUMBER

FROM phone_log a

WHERE a.SOURCE_PHONE_NUMBER = pl.SOURCE_PHONE_NUMBER

AND a.CALL_START_DATETIME =

(SELECT MAX (CALL_START_DATETIME)

FROM phone_log b

WHERE b.SOURCE_PHONE_NUMBER =

pl.SOURCE_PHONE_NUMBER)),

(SELECT DESTINATION_PHONE_NUMBER

FROM phone_log a

WHERE a.SOURCE_PHONE_NUMBER = pl.SOURCE_PHONE_NUMBER

AND a.CALL_START_DATETIME =

(SELECT MIN (CALL_START_DATETIME)

FROM phone_log b

WHERE b.SOURCE_PHONE_NUMBER =

pl.SOURCE_PHONE_NUMBER))),

'N',

'Y')

AS flag

FROM phone_log pl

For SQL Test : Sample Questions from Amazon Test (2/2)

18 comments:

SathishFebruary 9, 2017 at 9:07 AM
For the 1st question, i think the answer is incorrect. They need products which are sold on both days. Your query may return products that were sold twice in a single day but not on both days.
imranJuly 23, 2017 at 7:30 PM
QUESTION NO 3 NOT WORKING. IT IS WORKING WITH CTE FOR THE HIGHER SOLD IN BOTH DAYS. PLEASE SEE BELOW
WITH PRODUCT_PER_DAY AS
(
SELECT ORDER_DAY, PRODUCT_ID, SUM(QUANTITY * PRICE) AS SOLD_AMOUNT
FROM [testDB].[dbo].[Order]
GROUP BY ORDER_DAY, PRODUCT_ID
)
SELECT ORDER_DAY, PRODUCT_ID, SOLD_AMOUNT
FROM PRODUCT_PER_DAY
WHERE (SOLD_AMOUNT) IN
(
SELECT MAX(SOLD_AMOUNT)
FROM PRODUCT_PER_DAY GROUP BY ORDER_DAY
)
ORDER BY ORDER_DAY, PRODUCT_ID
UnknownAugust 3, 2017 at 4:58 PM
Question No 3 not working . It is working with

SELECT PRODUCT_ID,
order_day
FROM
(
SELECT P.PRODUCT_ID,
P.order_day,
RANK() OVER (
PARTITION BY P.order_day
ORDER BY sum(QUANTITY*PRICE) DESC
) RNK
FROM orders_temp P

) A
WHERE RNK = 1;

UnknownOctober 13, 2017 at 12:29 PM
This comment has been removed by the author.
theloveandlightstoreMay 23, 2019 at 2:47 PM
This blog is really helpful regarding all educational knowledge I earned. It covered a great area of subject which can assist a lot of needy people. Everything mentioned here is clear and very useful.
Encoder strip for HP Designjet T120
Amit OneSeptember 20, 2019 at 7:14 PM
This comment has been removed by the author.
Amit OneSeptember 20, 2019 at 7:15 PM
#Q 1
#products sold on both 7/1 and 7/2, number of times sold
select product_id, sum(quantity) as qty_sold
from sales.order
where product_id in (
select distinct a.product_id
from sales.order a
join sales.order b
on a.product_id = b.product_id
and b.order_date = '2011-07-02'
where a.order_date = '2011-07-01'
order by a.product_id
)
group by product_id;
Amit OneSeptember 20, 2019 at 7:21 PM
#Q - 2
#Get product ordered on 2 but not on 1
select distinct product_id
from sales.order
where order_date = '2011-07-02'
and product_id not in (
select distinct product_id from sales.order
where order_date = '2011-07-01');
Amit OneSeptember 20, 2019 at 7:44 PM
#Q - 3
#highest sold Products (Qty* Price) on both days
#Get product sold for both days, sum
#Get max for each day
#Union

WITH cte_sale (product_id, order_date, sale)
AS (
select product_id, order_date, (quantity * price) as sale
from sales.order
where order_date in ('2011-07-01', '2011-07-02')
group by product_id, order_date),

cte_sale_day1 (product_id, order_date, sale)
AS (
select product_id, order_date, max(sale)
from cte_sale
where order_date = '2011-07-01'),

cte_sale_day2 (product_id, order_date, sale)
AS (
select product_id, order_date, max(sale)
from cte_sale
where order_date = '2011-07-02')

select product_id, order_date, sale from cte_sale_day1
union
select product_id, order_date, sale from cte_sale_day2;

Amit OneSeptember 24, 2019 at 5:30 AM
#Q4 - Get me for all products, total sales on 01-JUL-11 and 02-JUL-11 adjacent to each other
select product_id,
sum(case when order_date = '2011-07-01' then quantity * price else 0 end) as sale_day1,
sum(case when order_date = '2011-07-02' then quantity * price else 0 end) as sale_day2
from sales.order
group by product_id
order by product_id;
Amit OneSeptember 24, 2019 at 6:58 AM
#Q5 - Get me all products daywise, that was ordered more than once
select order_date, product_id, count(product_id)
from sales.order
group by product_id, order_date
having count(product_id) > 1
order by product_id
AyChan87November 6, 2019 at 8:05 AM
This comment has been removed by the author.
AyChan87November 6, 2019 at 8:06 AM
Q7:

SELECT
ROUND((electronics_sold/books_sold)*100,0) AS pct_sold
FROM
(
SELECT
SUM(
CASE
WHEN p.product_group = 'Electronics'
THEN s.sales_amt
ELSE 0
END) AS electronics_sold ,
SUM(
CASE
WHEN p.product_group = 'Book'
THEN s.sales_amt
ELSE 0
END ) AS books_sold
FROM
bi_adhoc.AMZ_SALESFACT s
INNER JOIN
bi_adhoc.AMZ_PRODUCTS p
ON
p.product_id = s.product_id)
govindNovember 11, 2019 at 6:09 PM
First answer is wrong. In case if you have the sale in second day for one product more then once even though it is not in first day. It would return that product also. which is wrong.

WITH CTE AS (
SELECT ORDER_DAY,PRODUCT_ID, COUNT(*) AS COUNQT
FROM ORDERS
GROUP BY 1,2)
SELECT PRODUCT_ID,SUM(COUNQT) AS ASDSD FROM CTE
GROUP BY 1
HAVING COUNT(*) > 1

this is my answer for the first question
UnknownNovember 21, 2019 at 8:07 AM
Q10: populate source ,destination and call_dtae if first called number and last called number are the same ?

what would be the query for this?
VanaOctober 12, 2020 at 11:50 PM
Q2 Minus didn't work.
Except works.
Select Product_ID FROM Orders
EXCEPT (Select Product_ID FROM Orders WHERE OrderDay='2011-07-01')

Informatica Solutions

Saturday, November 28, 2015

SQL Test : Sample Questions from Amazon Test (1/2)

18 comments:

Pages