Clicks per Domain From Raw URLs

SQL practice question · Hard · Strings · Google

Extract the domain from clicked_url: trim spaces and lower-case it; remove the scheme (anything up to and including '://', if present); remove ONE leading 'www.' if present; then cut at the first '/', '?', '#' or ':' (port). Queries are compared after LOWER(TRIM(query)). Return domain, clicks and distinct_queries. Order by clicks DESC, then domain.

Table: search_clicks

Schema of search_clicks
Column	Type
click_id	INTEGER
query	TEXT
clicked_url	TEXT

Schema of search_clicks

Column

Type

click_id

INTEGER

query

TEXT

clicked_url

TEXT

Sample rows of search_clicks
click_id	query	clicked_url
1	sql joins	https://www.techdocs.io/sql/joins
2	sql joins	http://techdocs.io/sql
3	window functions	https://blog.datahub.dev/windows?ref=g
4	SQL joins	https://www.techdocs.io
5	cte	https://blog.datahub.dev/cte
6	pivot	https://qa.forum.net/q/123

Sample rows of search_clicks

click_id

query

clicked_url

sql joins

https://www.techdocs.io/sql/joins

sql joins

http://techdocs.io/sql

window functions

https://blog.datahub.dev/windows?ref=g

SQL joins

https://www.techdocs.io

cte

https://blog.datahub.dev/cte

pivot

https://qa.forum.net/q/123

Expected output on the sample data
domain	clicks	distinct_queries
techdocs.io	3	1
blog.datahub.dev	2	2
qa.forum.net	1	1

Expected output on the sample data

domain

clicks

distinct_queries

techdocs.io

blog.datahub.dev

qa.forum.net

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.