Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Speeding Up Large Joins
00:00
5 left

Speeding Up Large Joins

MediumSQL · PostgreSQL

Problem

What would you suggest to speed up joins on large data sets?

Asked in the online SQL technical challenge stage. 6th question of the offline challenge; first five were typical SQL query questions.

You are given two AWS billing tables and need to compare join patterns. Write a SQL query that returns the join performance guidance for each join pattern in the sample data.

Output

  1. One row per join pattern
  2. Columns: join_pattern, recommended_action, reason
  3. Include only rows where the pattern is active
  4. Sort by join_pattern ascending

Output details

  • Base the recommendation on estimated_rows_after_join.
  • For values greater than 1000000, return Add indexes on join keys and filter earlier with its corresponding large-row-count reason.
  • For values greater than 100000 through 1000000, return Review join order and reduce input rows with its corresponding moderate-row-count reason.
  • For values 100000 or below, return Current join strategy is acceptable with its corresponding smaller-join reason.
  • Treat NULL estimated_rows_after_join values as Current join strategy is acceptable with the smaller-join reason.

Schema

join_patterns
ColumnTypeDescription
pattern_idPKINTPrimary key for the join pattern
join_patternVARCHAR(50)Short name for the join pattern
estimated_rows_after_joinBIGINTEstimated number of rows produced by the join
is_activeBOOLEANWhether the pattern is currently relevant
Tablesjoin_patterns
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the one table are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.