--- title: "Example SQL" slug: "features-market-data-gap-example-sql" updated: 2026-07-01T14:58:40Z published: 2026-07-01T14:58:40Z canonical: "docs.fmad.io/features-market-data-gap-example-sql" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.fmad.io/llms.txt > Use this file to discover all available pages before exploring further. # Example SQL ## Summary For use with MDGap summary parq files list ### High level Total Packets and Gaps ```plaintext duckdb -c "select make_timestamp_ns(min(ts_pcap_min)) as TSStart, (max(ts_pcap_max) - min(ts_pcap_min))/(24*60*60e9) as Days, proto,mcgroup,port,count(DISTINCT source) as PCAPFiles,sum(gap_cnt) as TotalGap,sum(pkt_byte)/1e9 as GB from read_parquet('./**/*.parq') group by proto,mcgroup,port order by port" ``` Example output ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(470).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=Hx98sNsp39U%2BxLkVj1cN1e4FwhuAI9c%2FpFLXjhPy0Qs%3D) ### List all files with Gaps Generates a list of all PCAP files which have gaps ```plaintext duckdb  -c "select source,proto,endpoint,mcgroup,port,gap_cnt from read_parquet('./**/*.parq') where (gap_cnt > 0)" ``` Example output ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(472).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=o6PhWTDxC5RUI1NfNmHsjCsCCUWFU1Z3aUrPkwNrna0%3D) ### List all files with Gaps on a specific Multicast Group Filter for gaps/drops on a specific multicast group ```plaintext duckdb  -c "select source,proto,endpoint,mcgroup,port,gap_cnt from read_parquet('./**/*.parq') \  where (gap_cnt > 0) and \    (MCGroup like '224.  0.206.  5%')" ``` Example output ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(473).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=yIkH15Wz2T%2Fm9BPZfsreEQriNksFZIsGBzATdoBMLgA%3D) ### List all files on a specific Port with a Timerange List all files on multicast port - 4502 - between 24th June 2026 8:55 and 09:05 AM EST ```plaintext duckdb -c "select  source, proto,endpoint,mcgroup,port,pkt_cnt, gap_cnt from read_parquet('./**/*.parq') \  where (port == 45002) and \    (ts_pcap_min > epoch_ns('2026-06-24 08:55:00'::TIMESTAMPTZ AT TIME ZONE 'EST')) and \    (ts_pcap_max < epoch_ns('2026-06-24 09:05:00'::TIMESTAMPTZ AT TIME ZONE 'EST'))  \  order by ts_pcap_min" ``` Example output ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(471).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=vWHPf1pmznszNH0I%2BlkrXmcSVIjcyUMY%2FCKb%2BtC3X48%3D) --- ## Gap Logs For use on MDGapLog .parq files, these files log each and every gap detected. ### List the largest top 10 Gaps found ```plaintext duckdb -c "select make_timestamp_ns(ts_pcap),source,proto,endpoint, mcgroup,port,seq_delta from read_parquet('./**/*.parq') order by seq_delta desc limit 10" ``` Example output ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(474).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=v0fMlraip2s5KpWh6IQhIveplNDrLkpb4RgaabvXLRc%3D) ### Summary per Multicast group of all Gaps ```plaintext duckdb -c "select make_timestamp_ns(min(ts_pcap)),endpoint,mcgroup,port,sum(seq_delta) as TotalGap,count(distinct source) as TotalPCAP from read_parquet('./**/*.parq') group by endpoint,mcgroup,port order by port" ``` Example Ouput ![](https://cdn.document360.io/b9aab0c4-36d0-4616-9673-30b661455571/Images/Documentation/image(475).png?sv=2026-02-06&spr=https&st=2026-11-21T00%3A25%3A03Z&se=2026-11-21T01%3A25%3A03Z&sr=b&sp=r&sig=epmjKSH9Cg%2Bpgymvsgb9HQBMbjt413MxYSJNpxZQYWQ%3D)