Effective Dated Entity Rows | Dataford Interview Questions

How many rows on an effective dated entity?

Assume the entity stores multiple versions of the same business record over time. Write a SQL query to return the number of rows for each entity, including all historical versions.

Output

One row per entity_id
Columns: entity_id, row_count
Include every entity present in the source data
Sort by entity_id ascending

Schema

effective_dated_entity

Column	Type	Description
entity_row_idPK	BIGINT	Surrogate key for each row
entity_id	VARCHAR(50)	Business identifier for the effective-dated entity
effective_start_date	DATE	Date when this version becomes effective
effective_end_date	DATE	Date when this version stops being effective, null for current row
status_code	VARCHAR(20)	Status of the version, such as active or inactive
last_updated_at	TIMESTAMP	Timestamp when the row was last updated

Your question is Effective Dated Entity Rows. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem