SQL Group by and Joins | Dataford Interview Questions

SQL: find the average and count per group in one table, join it with another table to get other properties, and order the result (full join, group by, count, avg, order by). Asked in the HackerRank online test stage. Q2.

Write the query using the provided payment and merchant tables.

Output

One row per merchant or payment group, including unmatched rows from either table.
Columns: merchant_id, merchant_name, country, payment_count, and average_amount.
Use zero for merchants without payments; retain NULL for an unavailable average.
Order by merchant_name ascending with NULLs last, then merchant_id ascending.

Schema

payment_events

Column	Type	Description
payment_idPK	INT	Unique payment event identifier
merchant_id	VARCHAR(20)	Merchant identifier associated with the payment
amount	NUMERIC(10,2)	Payment amount

merchant_accounts

Column	Type	Description
merchant_idPK	VARCHAR(20)	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
country	VARCHAR(2)	Two-letter merchant country code

Your question is SQL Group by and Joins. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem