Microsoft: Customers Billed for Both Azure and Microsoft 365
A Microsoft commercial-sales analyst is sizing the cross-sell base: customers who pay for both the Azure cloud and Microsoft 365.
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #788
Problem statement
A Microsoft commercial-sales analyst is sizing the cross-sell base: customers who pay for both the Azure cloud and Microsoft 365.
Return every customer with at least one Azure invoice and at least one Microsoft 365 invoice, as customer_name, azure_total (sum of their Azure invoices) and m365_total (sum of their Microsoft 365 invoices). Return the rows in any order.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: Customer
| Column | Type |
|---|---|
| customer_id | int |
| customer_name | varchar |
Primary key: customer_id.
One row per commercial customer.
Table: AzureInvoice
| Column | Type |
|---|---|
| invoice_id | int |
| customer_id | int |
| invoice_month | char |
| amount | int |
Primary key: invoice_id.
One row per monthly Azure invoice (invoice_month as 'YYYY-MM'); amounts in US dollars.
Table: M365Invoice
| Column | Type |
|---|---|
| invoice_id | int |
| customer_id | int |
| invoice_month | char |
| amount | int |
Primary key: invoice_id.
One row per monthly Microsoft 365 invoice.
Examples
Example 1
Customer
| customer_id | customer_name |
|---|---|
| 1 | Contoso |
| 2 | Fabrikam |
| 3 | Northwind Traders |
| 4 | Tailspin Toys |
AzureInvoice
| invoice_id | customer_id | invoice_month | amount |
|---|---|---|---|
| 1 | 1 | 2025-01 | 1000 |
| 2 | 1 | 2025-02 | 2000 |
| 3 | 1 | 2025-03 | 1500 |
| 4 | 2 | 2025-01 | 800 |
| 5 | 4 | 2025-02 | 2500 |
M365Invoice
| invoice_id | customer_id | invoice_month | amount |
|---|---|---|---|
| 1 | 1 | 2025-01 | 300 |
| 2 | 1 | 2025-02 | 300 |
| 3 | 3 | 2025-01 | 900 |
| 4 | 4 | 2025-01 | 100 |
| 5 | 4 | 2025-02 | 200 |
| 6 | 4 | 2025-03 | 300 |
Output
| customer_name | azure_total | m365_total |
|---|---|---|
| Contoso | 4500 | 600 |
| Tailspin Toys | 2500 | 600 |
How to solve Microsoft: Customers Billed for Both Azure and Microsoft 365
The trap here is fan-out. Joining Customer to AzureInvoice and M365Invoice in one go produces one row per pair of invoices: Contoso's three Azure invoices meet its two Microsoft 365 invoices six times, so a SUM after that join reports $9,000 of Azure instead of $4,500, and $900 of Microsoft 365 instead of $600. The numbers look plausible, which is what makes the bug dangerous.
The fix is to aggregate each fact table to one row per customer before joining. Then each customer meets at most one Azure total and one Microsoft 365 total, and inner joins to both keep exactly the customers who have each kind of invoice — the "both" condition costs nothing extra.
Two alternatives: scalar subqueries per customer guarded by two EXISTS, and a UNION ALL of both tables tagged by source, pivoted with conditional sums and filtered with HAVING. All three read each invoice table once.
Reference solution (MySQL)
SELECT c.customer_name, a.azure_total, m.m365_total
FROM Customer c
JOIN (SELECT customer_id, SUM(amount) AS azure_total FROM AzureInvoice GROUP BY customer_id) a
ON a.customer_id = c.customer_id
JOIN (SELECT customer_id, SUM(amount) AS m365_total FROM M365Invoice GROUP BY customer_id) m
ON m.customer_id = c.customer_idAnother way
SELECT c.customer_name,
(SELECT SUM(amount) FROM AzureInvoice a WHERE a.customer_id = c.customer_id) AS azure_total,
(SELECT SUM(amount) FROM M365Invoice m WHERE m.customer_id = c.customer_id) AS m365_total
FROM Customer c
WHERE EXISTS (SELECT 1 FROM AzureInvoice a WHERE a.customer_id = c.customer_id)
AND EXISTS (SELECT 1 FROM M365Invoice m WHERE m.customer_id = c.customer_id)Another way
SELECT c.customer_name, SUM(CASE WHEN b.src = 'azure' THEN b.amount ELSE 0 END) AS azure_total,
SUM(CASE WHEN b.src = 'm365' THEN b.amount ELSE 0 END) AS m365_total
FROM Customer c
JOIN (SELECT customer_id, amount, 'azure' AS src FROM AzureInvoice
UNION ALL SELECT customer_id, amount, 'm365' AS src FROM M365Invoice) b ON b.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(CASE WHEN b.src = 'azure' THEN 1 END) > 0 AND COUNT(CASE WHEN b.src = 'm365' THEN 1 END) > 0← Microsoft: Top Human Contributor per GitHub Repository in 2025 · Microsoft: Teams Daily Stickiness (DAU/MAU) in March 2025 →