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
Database Sharding and Partitioning
00:00
5 left

Database Sharding and Partitioning

HardSQL · PostgreSQL

Problem

Explain your approach to database sharding and partitioning.

For this SQL exercise, produce a distribution report that helps evaluate a sharding and partitioning design. Include active shards and their monthly event activity for the specified reporting period, including combinations with no qualifying events.

Output

  1. One row per active shard and reporting partition.
  2. Columns: shard_id, shard_name, partition_name, event_count, total_amount, prior_event_count, and cumulative_event_count.
  3. Include completed events from January through March 2025, exclude cancelled or undated events, and show zeroes for missing combinations.
  4. Order by shard_id, then partition month.

Schema

shards
ColumnTypeDescription
shard_idPKINTUnique shard identifier
shard_nameVARCHAR(50)Logical shard name
regionVARCHAR(30)Deployment region
activeBOOLEANWhether the shard accepts traffic
partition_catalog
ColumnTypeDescription
partition_namePKVARCHAR(60)Logical partition name
month_startDATEInclusive month boundary
month_endDATEExclusive month boundary
tenant_shards
ColumnTypeDescription
tenant_idPKINTTenant identifier
shard_idINTAssigned shard
assigned_atDATEDate of shard assignment
order_events
ColumnTypeDescription
event_idPKINTUnique event identifier
tenant_idINTOwning tenant
occurred_atDATEEvent date
amountNUMERIC(10,2)Event amount
statusVARCHAR(20)Event processing status
Tablesshardspartition_catalogtenant_shardsorder_events
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the four tables 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.