SQL Challenges-1: Order ID's that executed by maximum number of salespersons

Last update on December 20 2024 11:39:19 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the order_id(s) that was executed by the maximum number of salespersons.
If there are, more than one order_id(s) executed by the maximum number of salespersons find all the order_id(s). Return order_id.

Input:

Table: salemast

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

DROP TABLE  IF EXISTS salemast;
CREATE TABLE salemast(salesperson_id int,  order_id int);
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5001', '1001');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5002', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5003', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5004', '1002');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5005', '1003');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5006', '1004');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5007', '1004');
INSERT INTO salemast(salesperson_id, order_id) VALUES ('5008', '1004');
SELECT order_id
FROM salemast
GROUP BY order_id
HAVING COUNT(DISTINCT salesperson_id) = (
    SELECT MAX(salesperson_count)
    FROM (
        SELECT COUNT(DISTINCT salesperson_id) AS salesperson_count
        FROM salemast
        GROUP BY order_id
    ) AS counts
); 

Sample Output:

order_id|
--------|
    1002|
    1004|	

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find Student Supporter.
Next: Big Cities.