Introduction: One Report, ¥30K Monthly Income

China has over 500 million home buyers and potential buyers. Their core pain point is clear: housing price data is scattered across multiple platforms (Beike, Lianjia, Anjuke), making cross-platform comparison impossible; and self-analysis is too time-consuming.

A solo developer named Ajie spotted this opportunity last year. Using DuckDB, he built a “City Housing Price Trend Analysis Report” product targeting young people preparing to buy homes and real estate investors. He now generates a stable income of 20,000-40,000 RMB per month, maintaining the entire system with less than 1 hour of actual work daily.

His core product is a PDF report priced at 99 RMB per copy, or a 299 RMB quarterly subscription.

Today I’ll break down his complete technical stack and path — the core tool is simply DuckDB, with near-zero running costs.

One. Product Architecture: From Zero to One

The core idea is straightforward: fetch public data → clean and analyze with DuckDB → generate visualizations → output PDF report → deliver via WeChat/WeChat Official Account.

┌─────────────┐ ┌──────────────┐ ┌──────────────┐ ┌─────────────┐
│ Data Layer │────▶│ DuckDB Layer │────▶│ Chart Layer │────▶│ Delivery │
│ (API/CSV) │ │ (Clean/Agg) │ │ (Matplotlib) │ │ (PDF/WeChat)│
└─────────────┘ └──────────────┘ └──────────────┘ └─────────────┘

Data Sources

All of Ajie’s data comes from public channels:

1. Fangtxian API: Historical price indices by city (monthly)
2. Lianjia Scraper: Second-hand home listing prices and transaction prices (daily)
3. Statistics Bureau Data: City population inflow/outflow, GDP growth rates
4. Land Trading Centers: Land transaction floor prices by city

These data formats vary wildly — CSV, JSON, HTML tables, Excel — and DuckDB’s strength is reading all of these formats directly without prior conversion.

Two. Core Technology: DuckDB Data Processing in Practice

Step 1: Multi-Source Data Merging

Ajie’s system first pulls all data sources into a data/raw/ directory. Then it reads them directly using DuckDB’s read_csv_auto and read_json functions:

import duckdb

# Connect to local DuckDB database
con = duckdb.connect("housing.db")

# Auto-read CSV files (with type inference)
con.execute("""
 CREATE TABLE lianjia AS
 SELECT * FROM read_csv_auto('data/raw/lianjia_*.csv', 
 header=true,
 auto_detect=true,
 columns={
 'city': 'VARCHAR',
 'district': 'VARCHAR',
 'community': 'VARCHAR',
 'price_per_sqm': 'DOUBLE',
 'area': 'DOUBLE',
 'rooms': 'INTEGER',
 'listing_date': 'DATE'
 }
 )
""")

# Read JSON API data
con.execute("""
 CREATE TABLE fangtianxia AS
 SELECT * FROM read_json_auto('data/raw/ftxz_index_*.json')
""")

# Read Excel data
con.execute("""
 CREATE TABLE tongji AS
 SELECT * FROM read_excel('data/raw/city_stats.xlsx')
""")

The key here is read_csv_auto and read_json_auto — they automatically detect delimiters, encoding, and column types, saving massive preprocessing time. For solo developers, time is money.

Step 2: Cross-Source Correlation Analysis

Next comes the most critical part — correlating data from different sources to find relationships between housing prices, population, GDP, and land supply.

-- Calculate 12-month average price trends by city
CREATE VIEW city_price_trend AS
SELECT 
 city,
 DATE_TRUNC('month', listing_date) AS month,
 AVG(price_per_sqm) AS avg_price,
 MEDIAN(price_per_sqm) AS median_price,
 COUNT(*) AS listing_count,
 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price_per_sqm) AS p25,
 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price_per_sqm) AS p75
FROM lianjia
WHERE listing_date >= CURRENT_DATE - INTERVAL '365 days'
GROUP BY city, DATE_TRUNC('month', listing_date);

-- Join with population data, calculate "price-to-income ratio"
CREATE VIEW city_analysis AS
SELECT 
 t.city,
 t.month,
 t.avg_price,
 t.median_price,
 t.listing_count,
 s.population_change, -- net population inflow
 s.gdp_growth, -- GDP growth rate
 s.land_supply_area, -- land supply area
 -- Price-to-income ratio = avg_price / (per_capita_GDP / avg_household_size)
 ROUND(t.avg_price * 3.0 / NULLIF(s.gdp_per_capita / 2.5, 0), 2) AS price_income_ratio
FROM city_price_trend t
LEFT JOIN tongji s ON t.city = s.city_name;

Note the use of MEDIAN, PERCENTILE_CONT and other statistical functions — DuckDB has rich built-in statistical capabilities, allowing you to do most data exploration without importing Pandas.

Step 3: Trend Forecasting and Anomaly Detection

The report also needs a “3-month forward price trend forecast”. Ajie uses a simple but effective SQL approach:

-- Use window functions for moving averages and month-over-month change rates
CREATE VIEW price_forecast AS
SELECT 
 city,
 month,
 avg_price,
 -- 7-period moving average (smooth noise)
 AVG(avg_price) OVER (PARTITION BY city ORDER BY month ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7,
 -- Month-over-month change rate
 ROUND((avg_price - LAG(avg_price, 1) OVER (PARTITION BY city ORDER BY month)) 
 / NULLIF(LAG(avg_price, 1) OVER (PARTITION BY city ORDER BY month), 0) * 100, 2) AS mom_pct,
 -- Year-over-year change rate
 ROUND((avg_price - LAG(avg_price, 12) OVER (PARTITION BY city ORDER BY month)) 
 / NULLIF(LAG(avg_price, 12) OVER (PARTITION BY city ORDER BY month), 0) * 100, 2) AS yoy_pct
FROM city_analysis;

-- Find cities with anomalous price fluctuations (MoM change > ±5%)
SELECT * FROM price_forecast
WHERE ABS(mom_pct) > 5
ORDER BY ABS(mom_pct) DESC;

The elegance of this approach: everything is done in SQL, with zero Python loops. DuckDB’s window functions are extremely performant — even processing millions of rows returns results in seconds.

Three. Traditional Approach vs. DuckDB Approach

Many developers initially choose this stack when building such projects:

DuckDB’s core advantage: it lets data analysts accomplish with SQL what previously required Python + multiple libraries — and does it 10x faster.

Four. Report Generation and Delivery

After analysis, Matplotlib generates charts and packages everything into a PDF:

import matplotlib.pyplot as plt
import duckdb

con = duckdb.connect("housing.db")

# Query Beijing price trend
df = con.execute("""
 SELECT month, avg_price, median_price, p25, p75
 FROM city_price_trend 
 WHERE city = 'Beijing'
 ORDER BY month
""").fetchdf()

# Plot price trend chart
fig, ax = plt.subplots(figsize=(12, 6))
ax.plot(df['month'], df['avg_price'], label='Avg Price', color='#6366f1')
ax.fill_between(df['month'], df['p25'], df['p75'], alpha=0.2, color='#6366f1', label='IQR')
ax.set_title('Beijing Second-Hand Home Average Price Trend (Last 12 Months)')
ax.set_ylabel('RMB/sqm')
ax.legend()
plt.savefig('output/beijing_trend.png', dpi=150, bbox_inches='tight')

# Package into PDF using ReportLab
from reportlab.lib.pagesizes import A4
from reportlab.pdfgen import canvas

c = canvas.Canvas('output/Housing_Report_Beijing.pdf', pagesize=A4)
width, height = A4
c.drawImage('output/beijing_trend.png', 50, height - 400, width=500, height=250)
c.drawString(50, height - 50, "Data: Lianjia, Fangtxian, Statistics Bureau | Analysis Date: July 2026")
c.save()

The final PDF delivered to users includes:

• Target city’s 12-month price trend
• Cross-district price comparison
• Population/GDP correlation analysis
• 3-month forward trend forecast
• Data-driven purchasing recommendations

Six. Key Success Factors

1. Data quality determines product ceiling — Ajie spent significant time cleaning Lianjia data: deduplication, missing value imputation, standardizing city names. DuckDB’s DISTINCT, COALESCE, and REGEXP_REPLACE were invaluable here.

2. Reports must be “understandable” — Not all users understand data analysis. Ajie added a “Purchasing Index” (composite score) to the reports, giving users an at-a-glance view of which cities are worth buying in.

3. Automation is critical — The full pipeline from data collection to report generation runs automatically, requiring only 1 hour of monitoring daily. Manual operation would make this system unprofitable.

4. Private domain operations matter more than technology — Ajie consistently publishes free housing analysis content on Xiaohongshu and Douyin, driving traffic to his Official Account for paid conversions. Technology is just leverage; traffic is the amplifier.

Seven. How You Can Replicate This

If you want to replicate this model, consider these directions:

• Go local: Pick a specific city or district and go deep rather than wide
• Differentiate: Add unique analytical dimensions (e.g., school district premiums, subway proximity impact)
• Productize: Template your reports to support user-chosen cities and metrics
• Scale: Integrate more data sources and expand city coverage

The entire tech stack compresses to: Python + DuckDB + Matplotlib + ReportLab — all open-source and free. The only investment needed is time and execution.

📖 The complete code repository and data cleaning scripts referenced in this article are published on duckdblab.org, including a ready-to-run Jupyter Notebook and Docker deployment guide. Want to systematically learn DuckDB applications in data products? duckdblab.org has a complete tutorial series from beginner to advanced.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。