Merge Overlapping License Periods

SQL practice question · Hard · Gaps & Islands · Adobe, Microsoft

Each license covers `start_date` to `end_date` inclusive. For each customer merge licenses that overlap OR are adjacent (a license starting the day after the coverage so far ends continues it) into continuous coverage periods. Note that one long license can cover several later short ones. Return `customer_id`, `coverage_start`, `coverage_end`, `days_covered` (inclusive day count). Order by `customer_id`, `coverage_start`.

Table: licenses

Schema of licenses
ColumnType
license_idINTEGER
customer_idINTEGER
start_dateDATE
end_dateDATE
Sample rows of licenses
license_idcustomer_idstart_dateend_date
112026-01-012026-01-31
212026-01-202026-02-15
312026-02-162026-02-28
412026-04-012026-04-30
522026-03-012026-03-10
Expected output on the sample data
customer_idcoverage_startcoverage_enddays_covered
12026-01-012026-02-2859
12026-04-012026-04-3030
22026-03-012026-03-1010

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

‹ Monthly Active Subscriptions and MRR · Teams DAU/MAU Stickiness ›

T
Tech Town
Log InSign Up Free
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
{{ totalCount }}
Questions
{{ companyCount }}
Companies
{{ prog.total.solved }}
Solved
Bookmark Question Companies Difficulty Category Status
{{ r.n }} - {{ r.title }} {{ c }} {{ r.difficulty }} {{ r.category }} {{ r.statusIcon }}
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.
← All questions

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

🏆 Contest {{ contestWeek }}
{{ cur.catLabel }} {{ cur.diffLabel }} {{ c }}
Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide 
{{ embedCode }}

{{ cur.statement }}

Table: {{ tb.name }}
ColumnType {{ c.name }}{{ c.type }}
{{ h }} {{ v.text }}
Expected output on the sample data:
{{ h }} {{ v.text }}
  • {{ h }}

No submissions yet. Press Evaluate to submit your query.
{{ s.verdict }} {{ s.meta }}
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 }}
  1. {{ e }}
⏱ {{ timerLabel }}
{{ fnHelpTitle }}
{{ fnHelpText }}
Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically
Output {{ out.status }}
  • {{ w }}
    •
{{ out.verdict }}
{{ out.detail }}
 
{{ out.error }}
Expected (sample data)
{{ h }}{{ v.text }}
Your output (sample data)
{{ h }}{{ v.text }}
{{ h }} {{ v.text }}
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.

Sign Up Free Log In