This problem statement is a part of our SQL Interview Questions series. In this series we are committed to bring out a collection of real-world SQL interview questions to help job seekers improve their coding skills and gain confidence.
To check all the interview questions available in this series, click here.
Problem Statement:
Write an SQL query to return an array of Order Id’s for every company. The output should contain the Company name, array of all order id’s for the company and the total order count. Sort the output by the number of orders for each company in descending order.
Table:
Company_Orders
Company OrderID OrderAmount
Ebay E4317 20
Flipkart F1972 50
Wayfair W1906 60
Ebay E1616 20
Alibaba A3644 20
Alibaba A2461 80
Ebay E2611 100
Amazon A1454 45
Amazon A2379 85
Ebay E1663 60
Flipkart F1829 50
Amazon A1305 30
Flipkart F2986 75
Wayfair W2632 100
Flipkart F4923 95
Amazon A3182 20
Amazon A1556 80
Flipkart F4661 10
Ebay E1300 65
Alibaba A1499 95
Wayfair W2307 90
Alibaba A1067 20
Amazon A1565 15
Amazon A4639 25
Amazon A3174 95
Solution:
1. Microsoft SQL Server
SELECT Company,
STRING_AGG(OrderID, ', ') AS Orders_Array,
COUNT(OrderID) AS Orders_Count
FROM Company_Orders
GROUP BY Company
ORDER BY COUNT(OrderID) DESC
STRING_AGG() is an aggregate function in SQL Server that takes all values from rows and concatenates them into a single string in the output result set. All row values are first converted into a string data type and then concatenated. The syntax for the function is:
STRING_AGG (expression, separator)To learn about STRING_AGG() function in detail, check the Microsoft’s documentation here.
2. PostgreSQL
SELECT Company,
ARRAY_AGG(OrderID) AS Orders_Array
COUNT(OrderID) AS Orders_Count
FROM Company_Orders
GROUP BY Company
ORDER BY COUNT(OrderID) DESC
ARRAY_AGG() is an aggregate function in PostgreSQL, it accepts a set of values and returns an array where each value in the input set becomes an element of the array. The syntax for the function is:
ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])For more information on this function, check the PostgreSQL documentation here.
Output:
MS SQL Server output is shared below.
Let us know your queries in the comment section below. We will keep adding more SQL interview questions in this series over time.
nice content and will share this with my buddies.
thanks again
Great article!