Troikaa Pharmaceuticals Senior Business Manager Interview Questions and Answers

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 your answer

Q1. What is agile methology?
Ans. 
Agile methodology is a project management approach that emphasizes flexibility, collaboration, and incremental progress.
• Focus on delivering working software in short iterations

• Embrace changing requirements

• Encourage frequent collaboration between team members

• Use feedback to continuously improve the product

• Common frameworks include Scrum and Kanban
Answered by AI
Add your answer
Q2. Write a user story and edge cases
Ans. 
User story for a shopping website
• As a user, I want to be able to add items to my cart

• As a user, I want to be able to view my cart and update quantities

• As a user, I want to be able to proceed to checkout and make a payment
Answered by AI
Add your answer

Q1. What do you think about your self
Add your answer
Q2. Tell me previous year experience in your company?
Add your answer
Q3. What do you about sales?
Ans. 
Sales is the process of persuading potential customers to buy products or services.
• Sales involves identifying potential customers and their needs

• It requires effective communication and negotiation skills

• Salespeople must be knowledgeable about the products or services they are selling

• Building relationships with customers is important for long-term success

• Sales strategies may vary depending on the industry and target mar...read more
Answered by AI
Add your answer

Q1. Ques.1 How many delivery boy have called customers more than 5 times?
Ans. 
The number of delivery boys who called customers more than 5 times is not provided.
• Data on the number of delivery boys who called customers more than 5 times is missing.

• Need to gather data on the number of delivery boys and their call logs.

• Analysis can be done once the data is available.
Answered by AI
View 5 more amswers
Q2. Question 2. Count distinct uniques names from the table
Ans. 
To count distinct unique names from a table, use the SELECT DISTINCT statement with the name column.
• Use SELECT DISTINCT to retrieve unique names

• Specify the name column in the SELECT statement

• Example: SELECT DISTINCT name FROM table_name

• Store the result in an array of strings
Answered by AI
Add your answer

Q1. What you prepared before coming to interview?
Ans. Confidence, as well as good look's of me, and carrying my mind we glowing thoughts.
Answered Anonymously
Add your answer
Q2. What's the performance and perfection got from your experience?
Ans. Where ever you go for job it's should think work is first then whatever it may be.
Answered Anonymously
Add your answer
Q3. Say about you anything?
Ans. I'm humein begin i didn't have any altra power to do anything but i have confidence to achieve every thing with my logics skills and presenting skillful performance to solve anything.
Answered Anonymously
Add your answer
Q4. Why the reason for doing job?
Add your answer
Q5. What you will do if you got 1 lakh?
Ans. 
I would invest the money in a diversified portfolio to ensure long-term growth.
• I would research and consult with financial experts to determine the best investment options.

• I would consider investing in stocks, mutual funds, and real estate.

• I would also set aside a portion of the money for emergency savings and paying off any outstanding debts.

• If there is any money left over, I would use it to treat myself to a small lu
Answered by AI
Add your answer

Q1. Tell about yourself/ Self introduction
Add your answer
Q2. What are the products in financial industry?
Ans. 
Financial industry offers a wide range of products including banking, insurance, investments, and loans.
• Banking products: savings accounts, checking accounts, credit cards, mortgages, loans

• Insurance products: life insurance, health insurance, property insurance, auto insurance

• Investment products: stocks, bonds, mutual funds, exchange-traded funds (ETFs), retirement accounts

• Loan products: personal loans, business loans,
Answered by AI
Add your answer

Q1. Tell me about yourself
Add your answer
Q2. Project explanation
Add your answer