SQL Data Structure Review | Dataford Interview Questions

Tell me about how you would review SQL data structures for correctness and performance.

Using the supplied PostgreSQL metadata snapshot, identify tables that need structural or performance review. Include key metadata and scan statistics so the findings can be investigated.

Output

One row per table requiring review.
Include table_name, correctness and performance statuses, primary-key counts, and scan counts.
Sort alphabetically by table_name.

Schema

schema_tables

Column	Type	Description
table_idPK	INT	Metadata table identifier
table_name	VARCHAR(100)	Name of the reviewed table
row_count	BIGINT	Estimated number of rows
requires_primary_key	BOOLEAN	Whether the table is expected to have a primary key

schema_columns

Column	Type	Description
column_idPK	INT	Metadata column identifier
table_id	INT	Referenced table metadata identifier
column_name	VARCHAR(100)	Reviewed column name
is_primary_key	BOOLEAN	Whether the column belongs to the primary key
is_nullable	BOOLEAN	Whether the column permits null values

query_scan_stats

Column	Type	Description
stat_idPK	INT	Scan-statistics record identifier
table_id	INT	Referenced table metadata identifier
sequential_scans	BIGINT	Observed sequential scan count
index_scans	BIGINT	Observed index scan count

Your question is SQL Data Structure Review. Start with the requirements and the three tables 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