Business Expansion

Business Expansion is one of the most reported questions in the HackerRank SQL (Intermediate) test. It is a GROUP BY on two columns plus a HAVING, with two joins to fetch names.

What the question asks

A company logs every contact between its agents (the people in user_account) and its customers in a contact table. Find every agent and customer pair that has been in contact more than once.

For each pair, show:

• the agent’s user id, first name and last name
• the customer’s id and name
• how many times they have been in contact

Sort the result by the user id, smallest first.

Tables

contact also has contact_type_id, contact_outcome_id, additional_comment, initiated_by_customer and initiated_by_user. You do not need any of them. The last two are just yes/no flags (0 or 1), not ids.

Solution

SELECT
    ua.id AS user_account_id,
    ua.first_name,
    ua.last_name,
    cu.id AS customer_id,
    cu.customer_name,
    COUNT(*) AS contact_count
FROM contact AS c
JOIN user_account AS ua ON ua.id = c.user_account_id
JOIN customer AS cu ON cu.id = c.customer_id
GROUP BY ua.id, ua.first_name, ua.last_name, cu.id, cu.customer_name
HAVING COUNT(*) > 1
ORDER BY ua.id ASC, cu.id ASC;

How it works

1. Start from contact. Each row is one contact between one agent and one customer.
2. Join for the names. user_account gives the agent’s first and last name. customer gives the customer’s name.
3. Group by the pair. Grouping by both the user id and the customer id puts all contacts between the same two people in one group. The name columns are in the GROUP BY too, because MySQL (with its default ONLY_FULL_GROUP_BY mode) and most other databases want every selected column either grouped or aggregated.
4. Keep pairs with more than one contact. COUNT(*) is the number of contacts in the group, and HAVING COUNT(*) > 1 drops the pairs that met only once.
5. Sort. The question only asks for user id ascending. I also sort by customer id so that one agent’s customers always come out in the same order. Several people report passing with only ORDER BY ua.id, so the second key is a safety net, not a requirement.

Mistakes that fail the hidden tests

• Grouping by the agent only. Then you count all contacts of an agent across every customer. In my sample data that gives Rohit 6 contacts, which is not what the question asks.
• Hard-coding ids from the sample output. One public repo has WHERE c.initiated_by_user = 4 AND c.customer_id = 7. It matches the visible sample and fails every hidden test. Never filter on values you saw in the sample.
• Joining on initiated_by_user. That column is a 0 or 1 flag. Joining it to user_account.id sends every flagged contact to user 1 and drops the rest. The agent’s id is in user_account_id.
• Forgetting the ORDER BY. MySQL 8 does not sort GROUP BY output. Without ORDER BY, my sample came out as user 2, 1, 1, 2.
• Using WHERE COUNT(*) > 1. That is a syntax error. Conditions on counts go in HAVING.

Try it on sample data

Sample rows I used. The edge cases: one pair with 3 contacts, agent Arjun who never contacts the same customer twice, and contact rows inserted out of order.

user_account

customer

contact

Expected output

Rohit and Kraus GmbH (contact 7) met once, and Arjun met two different customers once each, so none of those pairs appear.