PROJECT_ID: SQL_01 // STATUS: DEPLOYED

The "Cottage" Economy Analysis

SQL (PostgreSQL) Python Data Analysis

01. The Mission

The post-2024 era saw a massive shift in workforce distribution. The question was not "Are people working remotely?" but "Are remote workers economically revitalizing rural areas?"

Objective: Analyze transaction data to identify "Digital Homesteads"—small towns with disproportionately high tech-spending and IP logins during working hours.

02. The Query

I utilized a Common Table Expression (CTE) to isolate weekday transaction volume in regions classified as 'Rural' by the census tract.

          -- Calculating the 'Zoom Town' Effect
          WITH Remote_Activity
            AS (
          
            SELECT
           region_id,
          
            COUNT(transaction_id)
            AS volume,
          
            AVG(transaction_amount)
            AS avg_spend
          
            FROM transactions
          
            WHERE day_of_week
            BETWEEN 1
            AND 5
          
            AND category
            IN ('Coffee', 'Co-Working',
            'Fiber_Internet')
          
            GROUP BY region_id
          )
          
          SELECT
           r.town_name,
           ra.volume,
          
            RANK()
            OVER (ORDER BY
            ra.volume DESC)
            AS growth_rank
          FROM Remote_Activity ra
          JOIN regions r
            ON ra.region_id = r.id
          WHERE r.population < 50000;
        

03. The Output

The data revealed that while major cities saw a 15% drop in coffee shop revenue, "Cottage Hubs" (towns 2 hours outside the city) saw a 240% increase in high-speed internet subscriptions.

+240%

Fiber Optic Demand

4.5 Days

Avg. Stay Duration

$12.5M

Shifted Revenue