get_trending_countries and get_trending_communities return 'no rows' for certain date ranges

[dadofsambonzuki]

BTC Map API Issue: get_trending_* Date Range Boundary Bug

Issue Summary

The get_trending_countries and get_trending_communities RPC methods fail to return data when queried for certain date ranges that should contain valid data.

Reproduction Steps

# This fails - returns "Query returned no rows"
curl -X POST https://api.btcmap.org/rpc \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <TOKEN>" \
  -d '{
    "jsonrpc": "2.0",
    "method": "get_trending_countries",
    "params": {
      "password": "<TOKEN>",
      "period_start": "2026-01-01",
      "period_end": "2026-01-31"
    },
    "id": 1
  }'

Test Results

January 2026 Date Range Testing

Date Range	Result	Notes
2026-01-01 to 2026-01-31	❌ "Query returned no rows"	Full month fails
2026-01-01 to 2026-01-05	✅ 10 countries	Early month works
2026-01-01 to 2026-01-09	❌ "Query returned no rows"	Boundary issue
2026-01-01 to 2026-01-10	❌ "Query returned no rows"	Boundary issue
2026-01-06 to 2026-01-31	❌ "Query returned no rows"	Mid-month fails
2026-01-08 to 2026-01-31	✅ 10 countries	Late month works
2026-01-15 to 2026-02-15	✅ 10 countries	Cross-month works
2026-01-20 to 2026-01-31	✅ 10 countries	Late month subset works

Observed Behavior

1. Split Data: Data exists in at least two non-contiguous periods:

   • Jan 1-5: Contains data (163 USA events)
   • Jan 8-31: Contains data (422 USA events)

2. Boundary Issue: Queries crossing certain dates return "no rows" even when:

   • Both start and end dates individually have data
   • The combined range should contain data

3. Inconsistent Results:

   • Jan 1-5 ✅
   • Jan 8-31 ✅
   • Jan 1-31 ❌ (should combine both periods)

Impact

January 2026 Blog Post Data Underreported:

Metric	Original (Partial)	Correct (Combined)	Difference
USA Events	422	1,050	+628
Brazil Events	69	143	+74
Swiss Communities	Not in top 10	72 events each	New entries

Workaround

Manually fetch data in segments and combine:

# Fetch in two periods
period1 = fetch_trending('2026-01-01', '2026-01-05')  # Early month
period2 = fetch_trending('2026-01-08', '2026-01-31')  # Late month

# Combine and aggregate
combined = merge_and_sum(period1, period2)

Questions to Investigate

1. Database Query: Is there a SQL query issue with date range boundaries?
2. Data Gaps: Is there actually no data for Jan 6-7, or is this a query artifact?
3. Other Months: Does this affect other months (December 2025, February 2026)?
4. Specific Dates: Which exact dates cause the boundary failure?

Related Code

• API endpoint: https://api.btcmap.org/rpc
• Methods affected: get_trending_countries, get_trending_communities
• Blog post with workaround: https://btcmap.org/blog/posts/2026-01

Suggested Fix

Investigate the SQL query used by these RPC methods. The issue likely relates to:

• Date boundary conditions in WHERE clauses
• Missing data for certain dates causing entire range to fail
• Aggregation logic that fails when data is sparse