# Natural Language Querying Across 48 Million Rows: Mention Leaderboards and Visualization in Practice

    This article walks through a real analysis of a public dataset of historical technical-community discussions containing roughly 48 million records. Instead of writing query scripts and drawing charts by hand, the analyst handed the data catalog to an AI coding assistant with a single intent-driven goal, and the assistant completed profiling, modeling, query execution, validation, and visualization in one session. The article details each stage, states the compliance prerequisites for working with public discussion data, explains what the reported "26 minutes" actually measured, and shares a corrected DuckDB SQL implementation with proper deduplication, annual share, and ranking. It also covers the systematic errors hidden in keyword matching, how to read a language-mention leaderboard without overclaiming, a reusable prompt template, and a general architecture for this class of analysis.

    1. Case Recap: From One Sentence to a Trend Report

    The subject is a public dataset of historical discussions from a technical community — about 48 million records. The analyst wrote no query scripts and drew no charts by hand. Instead, the data catalog was handed to an AI coding assistant with a single goal that was precise about intent and deliberately silent on details: count how often several programming languages were mentioned in community discussions year by year, and present the result as charts.

    The assistant worked through five stages on its own:

    1. Data profiling — unpack the archive; confirm directory layout, file formats, field semantics, the type of the time field, and missing or anomalous values.
    2. Analysis modeling — define the counting unit (title and body combined), the time granularity (calendar year), and the comparison dimension (programming language).
    3. Query execution — pick a columnar analytical database, write and run the aggregation query, and scan all ~48 million records.
    4. Result validation — reconcile annual totals against the detail rows and check for anomalous spikes.
    5. Visualization — generate an interactive web page with switchable multi-language comparison, and supplement with external statistics for information added after the dataset’s cutoff.

    Compliance comes first

    Public discussion data is not unrestricted data. Three things need to be confirmed before any work starts:

    • Source and license — which site the dataset came from and under what license it was published (for example, the Stack Exchange data dump is released under CC BY-SA 4.0), and whether attribution and share-alike obligations apply.
    • De-identification — keep only the fields the analysis requires; irreversibly hash or simply drop author identifiers; do not persist usernames, avatars, profile links, or any other information that can be traced back to a person.
    • Display boundaries — visualizations show aggregate results only, never original text snippets that could locate an individual; internal data must be de-identified first, and the authorized scope requested according to its sensitivity level.

    What the “26 minutes” actually measured

    The case took roughly 26 minutes, and that figure holds only under the conditions of that run: an 8-core, 16 GB laptop; DuckDB 1.x; a cold-cache first read of a compressed CSV; about a dozen trial-and-error iterations and reruns; and only four columns touched by the query. Change any of those — a warm cache, columnar Parquet files, a machine with more memory — and the result will differ substantially. It is not a general benchmark and not a performance commitment about any specific tool. For reproducibility, record the hardware, engine version, cache state, and number of queries together.

    2. Stage by Stage: Semantics, Profiling, Querying, Validation

    2.1 Semantic parsing: turning a vague goal into an explicit definition

    “Mentions of a language over the years” contains at least four undefined elements:

    • Counting unit — title, body, or both combined? Combining them pulls in passing mentions from long posts.
    • Time basis — split by calendar year, or a rolling 12-month window?
    • Counting rule — if a discussion mentions a language several times, does that count once or many times?
    • Comparison dimension — compare languages within the same year, or track each language’s growth over time?

    The case used “number of discussions that mention the language name.” That definition is reasonable but not the only one. If the real question is “what is the community seriously discussing,” you need additional filters such as a length threshold or a score threshold. The more precise the goal statement, the more trustworthy the result.

    2.2 Data profiling: understand it before you touch it

    Common traps: the timestamp hidden in body text or in filenames; mixed Chinese-English text with inconsistent encoding; duplicate coverage caused by reposts and incremental updates; and a dataset cutoff date. This case identified the cutoff and added supplementary information outside the main analysis. The practice is sound, but the supplementary data does not share the original dataset’s definition — its value lies mainly in showing trend continuity, not in producing a complete distribution.

    2.3 Query generation and execution

    A columnar engine has three defining traits: it reads only the columns you ask for, it processes batches in a vectorized way, and columnar compression reduces I/O.

    Execution notes:

    • For text files such as CSV, declare field types explicitly (TIMESTAMP/BIGINT/VARCHAR) so that faulty type inference does not trigger implicit conversion.
    • Put the time filter directly on the time field. Do not wrap the field in a function, or you lose partition pruning and statistics-based pruning.
    • After concatenating title and body, match on the combined string — this is more accurate than counting each separately and adding the results.
    • After matching, you must deduplicate by post ID. This is the step most often missed; it is explained separately below.
    • When the data lives in object storage, remote reads can query it directly, skipping the load step.

    2.4 Result validation: three routine checks

    1. Total reconciliation — the sum over all languages and years plus the records with no match should equal the dataset total (excluding records whose year cannot be determined).
    2. Sample validation — randomly pick a year and a language, read the original text manually, and verify the hit count.
    3. Comparison validation — recompute under a different definition (per-post vs. per-occurrence, title vs. body) and check whether the ranking stays stable.

    3. Corrected SQL: Dedup, Ranking, and Share

    The original approach had two issues that would distort the results, and both are fixed here. Counting each discussion only once requires deduplicating on (id, lang) — COUNT(*) alone cannot do it. And ranking should be computed directly on the annual summary table, rather than depending on window sorting over a subquery.

    1. -- Engine: DuckDB 1.x (columnar, supports read_csv / ILIKE / GROUP BY ALL)
    2. -- ${DATA_DIR} is substituted by the caller at runtime
    3. CREATE OR REPLACE VIEW raw AS
    4. SELECT * FROM read_csv(
    5. '${DATA_DIR}/posts.csv.gz',
    6. header = true,
    7. columns = { 'id': 'BIGINT', 'created_at': 'TIMESTAMP',
    8. 'title': 'VARCHAR', 'text': 'VARCHAR', 'score': 'INTEGER' }
    9. );
    10. -- Keyword table: alias -> canonical name (illustrative; production needs
    11. -- ongoing maintenance and sampling-based calibration)
    12. CREATE OR REPLACE TABLE kw(keyword, lang) AS VALUES
    13. ('rust', 'Rust'), ('rust-lang', 'Rust'),
    14. ('golang', 'Go'), ('go', 'Go'),
    15. ('python', 'Python'), ('php', 'PHP'),
    16. ('cpp', 'C++'), ('c++', 'C++');
    17. -- Key step: deduplicate by (id, lang) after matching.
    18. -- If one post matches multiple aliases of the same language (go and golang),
    19. -- only one row survives.
    20. CREATE OR REPLACE TABLE hit AS
    21. SELECT DISTINCT
    22. r.id,
    23. k.lang,
    24. EXTRACT(year FROM r.created_at) AS y
    25. FROM raw r
    26. JOIN kw k
    27. ON coalesce(r.title, '') || ' ' || coalesce(r.text, '')
    28. ILIKE '%' || k.keyword || '%';
    29. -- Annual summary: post count, share, rank, year-over-year
    30. CREATE OR REPLACE TABLE yearly AS
    31. WITH per_lang AS (
    32. SELECT lang, y, COUNT(*) AS n FROM hit GROUP BY lang, y
    33. ),
    34. -- The denominator is "all posts in that year", not the sum of per-language mentions
    35. base AS (
    36. SELECT EXTRACT(year FROM created_at) AS y, COUNT(*) AS posts
    37. FROM raw GROUP BY 1
    38. )
    39. SELECT p.lang,
    40. p.y,
    41. p.n,
    42. p.n * 1.0 / b.posts AS share,
    43. DENSE_RANK() OVER (PARTITION BY p.y ORDER BY p.n DESC) AS rk,
    44. p.n * 1.0 / NULLIF(LAG(p.n) OVER (PARTITION BY p.lang ORDER BY p.y), 0) - 1 AS yoy
    45. FROM per_lang p
    46. JOIN base b ON b.y = p.y;
    47. SELECT * FROM yearly WHERE lang IN ('Rust', 'PHP') ORDER BY y, rk;

    The cross-check query below counts occurrences. It exists to validate the definition and to contrast with the per-post result:

    1. SELECT k.lang, EXTRACT(year FROM r.created_at) AS y, COUNT(*) AS times
    2. FROM raw r
    3. JOIN kw k ON coalesce(r.title,'') || ' ' || coalesce(r.text,'')
    4. ILIKE '%' || k.keyword || '%'
    5. GROUP BY k.lang, EXTRACT(year FROM r.created_at);

    Engine and version differences

    Capability DuckDB 1.x PostgreSQL 16 ClickHouse 24.x
    Read compressed CSV directly read_csv('x.csv.gz', columns=...) Not supported; use file_fdw or COPY FROM PROGRAM and land the file first file('x.csv.gz', CSVWithNames)
    Case-insensitive matching ILIKE ILIKE positionCaseInsensitiveUTF8(), or lower() + LIKE
    Group-by shorthand GROUP BY ALL supported Not supported; list all fields Better to list columns explicitly
    Escaping ESCAPE '\' Same Same

    Two implementation details worth adding:

    • Only % and _ are wildcards. + and # are not, so 'c++' needs no escaping. Escaping is only necessary when the keyword itself contains % or _ (for example c_), in which case write ILIKE '%c\_%' ESCAPE '\'.
    • State the ranking definition. DENSE_RANK here is computed across all languages for each year. If upstream keeps only the top N and ranks after truncation, ties and positions may differ slightly from the truncated leaderboard — say in the report which one you used.
    • Performance trade-off — the example uses row-by-row ILIKE for readability. If matching becomes the bottleneck in production, extract keywords first, compress long text into a searchable list of tokens, and aggregate from there.

    4. How to Read the Leaderboard: Five Questions and Common Concerns

    The case’s chart showed Python first, Rust second, C++ third, and Go fourth, with PHP falling to thirteenth. The “emerging systems programming language” referenced here is Rust.

    One: mention volume is not usage. A new release or a controversy pushes topic volume up, while a mature toolchain has “nothing much to talk about.” Heat reflects how community attention is distributed, not engineering market share.

    Two: one community is not the whole industry. A community oriented toward systems programming or infrastructure will show a language distribution that systematically deviates from the overall developer population.

    Three: keyword matching carries systematic error. Short tokens produce false hits (a standalone go is usually the English verb, and a single letter c is unreliable); casing and spelling variants (postgres / PostgreSQL) must be mapped consistently; negation (“I gave up on that language”) gets counted as a positive mention. The remedy is to maintain an “alias → canonical name” mapping table and calibrate it with manual sampling. Do not trust naive matching.

    Four: share matters more than rank. When total discussion volume grows year over year, absolute counts for every language can rise together. The SQL above returns both share and yoy so you can see changes in relative structure.

    Five: popular does not mean appropriate. Selection also depends on ecosystem maturity, talent availability, maintenance cost, and fit for the use case.

    Common questions

    Q: What exactly is the definition of mention volume?
    A: By default it is “the number of discussions containing the keyword after title and body are combined, with each post counted once per language.” Any other definition must be stated explicitly.

    Q: How do I verify that deduplication actually worked?
    A: Compare SELECT COUNT(*) FROM hit against COUNT(DISTINCT id) per language, then compare with the per-occurrence result — per-occurrence must always be greater than or equal to per-post.

    Q: Why does the ranking differ from an official leaderboard?
    A: Any difference in data source, time window, keyword table, dedup method, or share denominator can change the ranking. To reproduce, align these five items first.

    Q: Can vote-weighted scores and sentiment classification be trusted directly?
    A: No. Vote counts carry a time-accumulation bias (older posts naturally have higher scores), reply counts are driven by controversy, and sentiment models drift on mixed-language and sarcastic text. Build a baseline with stratified manual annotation, and cross-validate against independent external indicators (job posting counts, package manager download volume, dependency repository statistics).

    5. A General Technical Approach

    Data layer — funnel everything into object storage through a single entry point, saved in open formats such as Parquet, CSV, or JSON; standardize time zones and field naming; organize into raw, detail, and summary layers.

    Query layer — prefer a columnar engine for aggregation; materialize the annual summary with CREATE OR REPLACE TABLE AS SELECT so dashboards read only the summary layer; allocate read-only accounts and separate workspaces by sensitivity level; add scheduled runs with anomaly alerts on metrics.

    Metric layer — mention count (absolute heat), annual share (structural change), year-over-year growth (inflection points), interaction-weighted depth (discussion depth), and sentiment tendency (distinguishing active debate from controversy). Every metric should carry its definition and known biases.

    Collaboration layer — keep data versions, queries, and run logs for reproducibility; label the data cutoff, the definition, and the keyword table version for traceability; de-identify and get approval before working with internal data for governance; and divide labor so the assistant handles profiling, coding, trial and error, and charting, while the engineer defines metrics, reviews definitions, and makes the judgment call.

    6. Prompt Template

    1. [GOAL]
    2. Analyze the trend in discussion volume for a class of topics across years.
    3. [DATA SCOPE]
    4. ${DATA_DIR}; first state the data source, license, and de-identification status;
    5. the output must note the data cutoff date.
    6. [DEFINITION]
    7. 1. Unit: match on merged title + body; deduplicate by (id, language),
    8. so one post counts once per language;
    9. 2. Keywords: use a centrally maintained "alias -> canonical name" mapping
    10. table and state its version;
    11. 3. Time: by calendar year;
    12. 4. Metrics: mention count, annual share (denominator = total posts that year),
    13. year-over-year growth.
    14. [EXECUTION REQUIREMENTS]
    15. 1. Engine and version: DuckDB 1.x (or another specified engine);
    16. provide an alternative for any unsupported function;
    17. 2. Profile the structure and field semantics before writing queries;
    18. keep the full SQL;
    19. 3. Materialize the annual summary as a result table.
    20. [VALIDATION REQUIREMENTS]
    21. 1. Reconcile summary values against the detail rows;
    22. 2. Manually review a random sample of no fewer than 30 records;
    23. 3. Provide both per-post and per-occurrence results and explain the difference;
    24. 4. State the hardware, engine version, cache state, and number of queries.
    25. [OUTPUT FORMAT]
    26. 1. Interactive web page: multi-topic switching, year filtering, hover values;
    27. 2. One-page conclusions: every claim backed by data, with the data cutoff,
    28. privacy handling, and limitations spelled out.

    7. Closing

    The value of this case is not a timing figure. It is that profiling, querying, validation, and charting can close the loop within a single interaction, which frees the analyst to return to problem definition and judgment.

    But a summary like “Rust rising, PHP fading” is only an observation about mention volume, under one specific community and one specific definition. Reliable insight requires clear definitions, traceable methodology, cross-validated evidence, and a clear-eyed understanding of data boundaries and compliance obligations.

    Tools determine how fast the analysis runs; method determines the quality of the conclusion. The capability most worth investing in is turning a vague business question into a rigorous, reproducible analysis workflow — and holding on to independent judgment behind a wall of numbers.