Microsoft: Customers Billed for Both Azure and Microsoft 365

Medium
788.

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.

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

ColumnType
customer_idint
customer_namevarchar

Primary key: customer_id.

One row per commercial customer.

Table: AzureInvoice

ColumnType
invoice_idint
customer_idint
invoice_monthchar
amountint

Primary key: invoice_id.

One row per monthly Azure invoice (invoice_month as 'YYYY-MM'); amounts in US dollars.

Table: M365Invoice

ColumnType
invoice_idint
customer_idint
invoice_monthchar
amountint

Primary key: invoice_id.

One row per monthly Microsoft 365 invoice.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Customer

customer_idcustomer_name
1Contoso
2Fabrikam
3Northwind Traders
4Tailspin Toys

AzureInvoice

invoice_idcustomer_idinvoice_monthamount
112025-011000
212025-022000
312025-031500
422025-01800
542025-022500

M365Invoice

invoice_idcustomer_idinvoice_monthamount
112025-01300
212025-02300
332025-01900
442025-01100
542025-02200
642025-03300

Output

customer_nameazure_totalm365_total
Contoso4500600
Tailspin Toys2500600
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
customer_idint
customer_namevarchar

Primary key: customer_id.

One row per commercial customer.

Table: AzureInvoice

ColumnType
invoice_idint
customer_idint
invoice_monthchar
amountint

Primary key: invoice_id.

One row per monthly Azure invoice (invoice_month as 'YYYY-MM'); amounts in US dollars.

Table: M365Invoice

ColumnType
invoice_idint
customer_idint
invoice_monthchar
amountint

Primary key: invoice_id.

One row per monthly Microsoft 365 invoice.

Examples

Example 1

Customer

customer_idcustomer_name
1Contoso
2Fabrikam
3Northwind Traders
4Tailspin Toys

AzureInvoice

invoice_idcustomer_idinvoice_monthamount
112025-011000
212025-022000
312025-031500
422025-01800
542025-022500

M365Invoice

invoice_idcustomer_idinvoice_monthamount
112025-01300
212025-02300
332025-01900
442025-01100
542025-02200
642025-03300

Output

customer_nameazure_totalm365_total
Contoso4500600
Tailspin Toys2500600

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_id

Another 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 →