Daily Cloud Cost Anomalies

SQL practice question · Hard · Window Functions · Microsoft

There is one cost row per subscription per day. baseline_avg = average cost of up to 7 PREVIOUS days of that subscription (the current day is not included). A day is an anomaly when at least 3 previous days exist in that window and cost is strictly greater than 2 * baseline_avg. Return subscription_id, cost_date, cost and baseline_avg (ROUND 2) for anomaly days. Order by subscription_id, cost_date.

Table: azure_daily_cost

Schema of azure_daily_cost
Column	Type
subscription_id	TEXT
cost_date	DATE
cost	REAL

Schema of azure_daily_cost

Column

Type

subscription_id

TEXT

cost_date

DATE

cost

REAL

Sample rows of azure_daily_cost
subscription_id	cost_date	cost
sub-1	2026-09-01	100
sub-1	2026-09-02	110
sub-1	2026-09-03	90
sub-1	2026-09-04	100
sub-1	2026-09-05	250
sub-1	2026-09-06	105
sub-1	2026-09-07	120
sub-1	2026-09-08	95

Sample rows of azure_daily_cost

subscription_id

cost_date

cost

sub-1

2026-09-01

100

sub-1

2026-09-02

110

sub-1

2026-09-03

sub-1

2026-09-04

100

sub-1

2026-09-05

250

sub-1

2026-09-06

105

sub-1

2026-09-07

120

sub-1

2026-09-08

Expected output on the sample data
subscription_id	cost_date	cost	baseline_avg
sub-1	2026-09-05	250	100
sub-1	2026-09-09	400	124.29

Expected output on the sample data

subscription_id

cost_date

cost

baseline_avg

sub-1

2026-09-05

250

100

sub-1

2026-09-09

400

124.29

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

Tech Town

Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog

SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest

Questions

Companies

Solved

Bookmark Question Companies Difficulty Category Status

{{ r.n }} - {{ r.title }}

No questions match these filters.

Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.

Daily challenge

{{ daily.title }} · {{ daily.difficulty }} Solve now

New question every day at midnight IST.

Your Progress

Reset all SQL practice progress{{ resetScope }}? This can't be undone.

{{ p.label }} ({{ p.pct }}%){{ p.solved }}/{{ p.total }}

Points: {{ myPoints }} · Easy 10 · Medium 20 · Hard 40

🔥 Longest streak: {{ streak.longest }}Current streak: {{ streak.current }}

Leaderboard (rolling 100 days)

Loading…

{{ u.rank }} {{ u.name }}{{ u.points }} pts

← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}

Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide

{{ embedCode }}

Table: {{ tb.name }}

ColumnType

Expected output on the sample data:

No submissions yet. Press Evaluate to submit your query.

The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn.

{{ solutionSql }}

SQL dialect ⏱ {{ timerLabel }}

{{ fnHelpTitle }}
{{ fnHelpText }}

Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically

Output

{{ out.error }}

Expected (sample data)

Your output (sample data)

Run your query to see the result here.

🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.