Malformed Mesh Node Addresses

Problem

A sensor mesh logs a ping every time a node answers, recording the node's dotted address. A well-formed address is four numbers separated by dots, where each number is between 0 and 255 and carries no leading zero (so `7` is fine but `07` is not). An address is **malformed** if any segment exceeds 255, if any segment has a leading zero, or if it does not have exactly four segments. For every distinct malformed `node_address`, return it together with `invalid_count`, the number of ping rows that carried it. Order by `invalid_count` descending, then by `node_address` descending.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns node_address, invalid_count.

Starter code (SQL)

SELECT *
FROM mesh_pings;

Related SQL questions

• 3-Consecutive-Week Marketing StreakHard
• 30-Day Rolling Average Trading VolumeHard
• 7-Day Active User Rolling MetricHard
• 7-Day Revenue Moving AverageHard
• Always Mid-Pack BakersHard
• Annual Energy Output Growth by Panel ModelHard