Three Clouds

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

The cloud_costs table holds one billed charge per row from AWS, GCP and Azure, and only the GCP and Azure invoices are being reconciled. List every charge amount on those invoices, from the smallest up to the largest.

AWS, GCP, Azure. Side by side.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

The trap hiding in plain sight

This is a vendor filter on a dirty label, dressed up as invoice reconciliation. Anyone can write SELECT amount FROM cloud_costs ... ORDER BY amount. What separates candidates is noticing that provider was written by exports with no casing convention, so the same vendor shows up as both 'GCP' and 'gcp'. Write the obvious WHERE provider IN ('gcp', 'azure') and the comparison is exact: you keep the 40 lowercase 'gcp' rows and silently drop every 'GCP' and 'Azure' charge, two thirds of what the reconciliation needed. Leave the vendor filter out and the 80 AWS charges leak in. Neither mistake raises an error.

How to get there

Step 1: Read provider before writing the filter

The sample rows show 'GCP' next to 'gcp' and 'AWS' next to 'aws'. That one observation is what the problem is testing, and it takes ten seconds to make.

Step 2: Normalize with LOWER(provider), then keep two vendors

Compare LOWER(provider) against 'gcp' and 'azure'. Every GCP and Azure spelling now matches, and both AWS spellings fall outside the list.

Step 3: Sort the amounts, credits first

Negative amounts are credits and stay in. Sorting amount ascending puts them at the top, which is how the reconciliation is read.

The solution

SELECT amount
FROM cloud_costs
WHERE LOWER(provider) IN ('gcp', 'azure')
ORDER BY amount;

Case-sensitive (wrong)

WHERE provider IN ('gcp', 'azure') matches only the lowercase 'gcp' rows: 40 of the 120 charges. Every 'GCP' and 'Azure' row disappears, and the query still runs cleanly.

Case-normalized (correct)

WHERE LOWER(provider) IN ('gcp', 'azure') folds every spelling to one form before comparing. All 120 GCP and Azure charges come back, and the AWS rows stay out.

Common follow-up questions

• If a future export wrote 'Gcp' or 'AZURE', would your query still match those rows? (Tests whether the normalization generalizes beyond the spellings in the sample.)
• How would you surface `provider` values that are none of the three vendors, so finance can catch typos? (Tests data-quality awareness beyond the happy path.)
• If this filter ran thousands of times a minute, how would you keep the case-insensitive match index-friendly? (Tests why a function on the column defeats a plain index and how to fix it.)