XYZ Distributors PMO Analyst Interview Questions and Answers for Experienced

• Q1. About previous experience situation based questions excel problem solving
  Add Answer
• Q2. Tell me about yourself difference between data quality and data validity
  Add Answer

• Q1. SQL query writing
  Add Answer
• Q2. Discussion on resume projects
  Add Answer

HackerRank OA on SQL. 3 questions, 1 Hour with webcam proctoring.

Question 1:
"""
1. The Good and The Bad City

There is a blood bank which maintains two tables: DONOR that contains information on the people who are willing to donate blood and ACCEPTOR, the people who are in need of blood. The bank wants to conduct a survey and find out the city that has the best and the worst donor sum amount/acceptor sum amount ratio. Both ratios are unique. That is, exactly one city has the best ratio and exactly one city has the worst ratio.
The donor sum amount is the total amount of blood, regardless of blood group, that people are ready to donate. The acceptor sum amount is the total amount of blood needed by that city.
There must be exactly two rows that denote the best and the worst ratios. The order of the rows does not matter. Each row must contain the following attributes:

1. The city's name (CITY).
2. The ratio ( donor sum amount / acceptor sum amount ), correct to 4 decimal places.

The schema of the two tables is given below:
Schema
DONOR
Name Type Description
ID Integer It is the ID of the donor.
NAME String It is the name of the donor.
GENDER Character It is the gender of the donor.
CITY String It is the city where the donor lives.
BLOOD_GROUP String It is the blood group of the donor.
AMOUNT Integer It is the amount of blood in pints, which the donator can donate.
ACCEPTOR
Name Type Description
ID Integer It is the id of the acceptor.
NAME String It is the name of the acceptor.
GENDER Character It is the gender of the acceptor.
CITY String It is the city where the acceptor lives.
BLOOD_GROUP String It is the blood group of the acceptor.
AMOUNT Integer It is the amount of blood in pints, which the acceptor needs.
Sample Data Tables
DONOR
ID NAME GENDER CITY BLOOG_GROUP AMOUNT
1 MARIA F Warne, NH AB+ 7
2 DOROTHY F East Natchitoches, PA AB+ 3
3 CHARLES M East Natchitoches, PA A- 6
4 DOROTHY F East Natchitoches, PA AB+ 9
5 MICHAEL M Warne, NH A+ 1
ACCEPTOR
ID NAME GENDER CITY BLOOG_GROUP AMOUNT
1 LINDA F Warne, NH A+ 9
2 CHARLES M Warne, NH AB+ 8
3 RICHARD M East Natchitoches, PA AB+ 3
4 LINDA F East Natchitoches, PA A+ 1
5 PATRICIA F Warne, NH A+ 5

Sample Output

East Natchitoches, PA 4.5000
Warner, NH 0.3636



Explanation

The amount of blood available for donation in East Natchitoches, PA is 3 + 6 + 9 = 18.

The amount of blood needed in East Natchitoches, PA is 3 +1 = 4.

Hence, the ratio is = ( 18 : 4 ) = 4.5000.

The amount of blood available for donation in Warne, NH is 1 + 7 = 8.

The amount of blood needed in Warne, NH is 9 + 8 + 5 = 22.

Hence, the ratio is = ( 8 : 22 ) = 0.3636.
"""

Question 2:
"""
2. Maximum Discount Product

A department store maintains data on customers, products, and purchase records in three tables: CUSTOMER, PRODUCT, and PURCHASE. The store manager wants to know which product is on maximum discount for each category. Write a query to print the following fields for each category, ordered by category, ascending: category, product ID and discount for the product that has the maximum discount in the category. In the case of multiple products having same maximum discount within a category, print the product with minimum product_id.


Table Schema
CUSTOMER
Name Type Description
CUSTOMER_ID Integer A customer's ID in the inclusive range [1, 500]. This is a primary key.
CUSTOMER_NAME String A customer's name. This field contains between 1 and 100 characters (inclusive).
CITY String A city name. This field contains between 1 and 50 characters (inclusive).
STATE String A state name. This field contains between 1 and 50 characters (inclusive).


PRODUCT
Name Type Description
PRODUCT_ID Integer A product's ID in the inclusive range [1, 500]. This is a primary key.
PRODUCT_NAME String A product's name. This field contains between 1 and 50 characters (inclusive).
CATEGORY String A category name of the product. This field contains between 1 and 50 characters (inclusive).
PRICE Integer The price of the product in the inclusive range [500, 1000].
DISCOUNT Integer The discount associated with the product in the inclusive range [5, 20].
AVAILABLE Integer The availability of a product. It is 1 if the product is available or it is 0 if the product is not available.

PURCHASE
Name Type Description
ID Integer An id associated with a purchase that is done in the inclusive range [1, 1000]. This is a primary key.
CUSTOMER_ID Integer A customer ID. This is a foreign key to customer.customer_id.
PRODUCT_ID Integer A product ID. This is a foreign key to product.product_id.
PURCHASE_DATE Date The date associated with a purchase. The date falls in the range '2000-01-01' to '2000-12-31' (inclusive).
'
Sample Case 0

Sample Input for Custom Testing
CUSTOMER
CUSTOMER_ID CUSTOMER_NAME CITY STATE
1 Pickett Wilhelm Park SD
2 Pickett Ipswich AZ
3 Poole Farwell KS
4 Pollard Bent Pine WV
5 Phelps Momford Landing VA


PRODUCT
PRODUCT_ID PRODUCT_NAME CATEGORY PRICE DISCOUNT AVAILABLE
1 P-1 C-5 720 10 1
2 P-2 C-1 935 17 1
3 P-3 C-2 588 19 1
4 P-4 C-4 619 5 0
5 P-5 C-1 803 16 1


PURCHASE
ID CUSTOMER_ID PRODUCT_ID PURCHASE_DATE
1 5 5 2000-06-04
2 1 5 2000-11-24
3 1 3 2000-10-02
4 5 1 2000-08-06
5 5 3 2000-06-22
6 3 1 2000-06-30
7 2 5 2000-01-10
8 2 1 2000-07-26
9 3 5 2000-09-23
10 4 5 2000-09-02
11 3 2 2000-05-25



Sample Output

C-1 2 17
C-2 3 19
C-4 4 5
C-5 1 10

Explanation

By referring to the sample data above, we find that:

For category C-1, there are two products P-2 and P-5 with discount 17 and 16 respectively. So the maximum discount is for product P-2 which is 17.
For category C-2, there is only one product P-3 with discount 19, so this is the product with maximum discount in this category.
For category C-4, there is only one product P-4 with discount 5, so this is the product with maximum discount in this category.
For category C-5, there is only one product P-1 with discount 10, so this is the product with maximum discount in this category.
"""

Question 3:
"""
3. Increase in Population

You are provided with the records of births and deaths during the course of years. Their records consist of a year and a type, either 'birth' or 'death'. Determine the year(s) when the population is highest versus the starting population. Return the year and the amount of the increase in population. If there is a tie, return the earliest of them.


Schema

You are provided 1 table: records.
records
Name Type Description
id int The unique id of a record.
type char(5) The type of record (birth/death).
year int The year in which the birth/death happened.
Sample Data Tables
records
id type year
2187 birth 2002
9941 death 2001
4361 birth 2003
6477 death 2001
3478 birth 2005
9719 death 2005
7292 birth 2002
4931 death 2005
5833 birth 2002
9379 death 2001
3472 birth 2003
5580 death 2003
4472 birth 2002
5915 death 2004
2624 birth 2003
5223 death 2005
7198 birth 2002
5384 death 2001
7660 birth 2004
5302 death 2005
5192 birth 2003
2537 death 2003
5260 birth 2003
7218 death 2004
2726 birth 2002
3856 death 2002
6594 birth 2003
9013 death 2005
4657 birth 2001
1782 death 2005
9744 birth 2004
5149 death 2001
2054 birth 2003
7423 death 2003
7156 birth 2002
2956 death 2001
3273 birth 2001
3721 death 2002
9756 birth 2002
6632 death 2003


OUTPUT
year count
2003 5


Explanation
year count cumulative
2001 -4 -4
2002 6 2
2003 3 5
2004 0 5
2005 -5 0

The highest cumulative increase in population is in the years 2003 and 2004. Return the earlier of the two, so the output is:
2003 5
"""

• Q1. No further communication received
  Add Answer

• Q1. What’s your previous experience
  View Answers (1)
• Q2. What’s your skills for the current role
  View Answers (1)

Puzzles case study sql probability questions

Cv and mostly same as round 1

• Q1. Cv and same as round 2
  Add Answer
• Q2. Case study on Amex ongoing issues
  Add Answer
• Q3. Geeks for geeks puzzle
  Add Answer
• Q4. Excel questions
  Add Answer
• Q5. Sql intermediate level questions
  Add Answer

• Q1. Knowledge about data summarisation.and some tools like Power bi and advance excel.
  Add Answer