I went with a LEFT JOIN approach, grouped by block_id, counted edits per block, then wrapped it in an outer query with a CASE WHEN to flag zero-edit blocks.
First, clarify the table schemas and the definition of 'never been edited' (e.g., no rows in edit_events for a block). Then, use a LEFT JOIN or NOT EXISTS to identify blocks without edits, and compute the percentage by dividing the count of such blocks by the total number of blocks, multiplying by 100.
Pro tip: Mention that you would confirm whether 'never been edited' means no edit events at all or no successful edits, and consider performance implications of different SQL patterns (e.g., NOT EXISTS vs LEFT JOIN) on large datasets.
Ask about the structure of blocks and edit_events tables, and confirm the exact definition of 'never been edited' (e.g., no rows in edit_events for a block).
Use a LEFT JOIN with a WHERE clause filtering NULLs, or a NOT EXISTS subquery, to select blocks that have no corresponding entries in edit_events.
Compute the total number of blocks and the number of unedited blocks, either in separate subqueries or using conditional aggregation.
Divide the count of unedited blocks by the total count, multiply by 100, and handle division by zero if the blocks table is empty.
Assemble the SQL query, ensuring correct rounding and aliasing, and discuss potential edge cases like duplicate edit events or NULL block IDs.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.