Task 1.1: Warmup

CSV data processing with DuckDB - Namecheap Premium Domains

Task Overview: Premium Domain Analytics

In this task, we’ll do a warmup exercise to get you familiar with DuckDB. You’ll analyze premium domains sold on Namecheap’s marketplace using DuckDB. You’ll work with CSV data containing domain availability information.

Learning Objectives

• Load and explore CSV data with DuckDB
• Analyze domain sales patterns and pricing
• Create aggregations and insights
• Find interesting cheap domain names

Data Overview

Understanding the Namecheap Dataset

The Namecheap Market Sales dataset contains domain sales information:

Dataset: Namecheap_Market_Sales_Buy_Now.csv

Data Fields:

• permalink: Full URL to the domain listing on Namecheap marketplace
• domain: The actual domain name (e.g., buythisdomain.com)
• price: Sale price of the domain in USD (e.g., 10000.00)
• extensions_taken: Number of domain extensions taken (e.g., .com, .net, .org)

Data Structure

The CSV file has the following structure:

permalink,domain,price,extensions_taken
https://www.namecheap.com/market/buynow/0000000000000000.info/,0000000000000000.info,10.00,3
https://www.namecheap.com/market/buynow/00000000000.online/,00000000000.online,10.00,11

Getting Started with CSV Data

Query and Explore the Dataset

Start by querying and exploring the CSV data.

Hint: Basic Exploration

-- Check the data structure
DESC 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv';

-- Query the CSV file
SELECT * FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv' LIMIT 10;

-- Count total records
SELECT COUNT(*) as count 
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv';

Basic Data Quality Check

Check for any obvious data quality issues.

Hint: Data Quality Check

-- Check for NULL values
SELECT 
    COUNT(*) as total_records,
    COUNT(CASE WHEN permalink IS NULL THEN 1 END) as null_permalinks,
    COUNT(CASE WHEN domain IS NULL THEN 1 END) as null_domains,
    COUNT(CASE WHEN price IS NULL THEN 1 END) as null_prices
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv';

Basic Price Analysis

Check the price ranges of the domains.

Hint: Price Analysis

-- Check price range
SELECT 
    MIN(price) as min_price,
    MAX(price) as max_price,
    AVG(price) as avg_price
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv';

Domain Analysis Tasks

Find Short Domain Names

Find domains that are 8 characters or less.

Hint: String Length Function

-- Use char_length() function to get domain length
SELECT *
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE char_length(domain) <= 8
ORDER BY price ASC;

Find Cheap Domains

Find domains that cost less than $100.

Hint: Price Filtering

-- Filter by price condition
SELECT *
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE price < 100
ORDER BY price ASC;

Find .co Domains

Find domains that contain the ‘.co’ extension.

Hint: String Contains Function

-- Use contains() function to check for '.co'
SELECT *
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE contains(domain, '.co')
ORDER BY price ASC;

Find Domains Without Hyphens

Find domains that don’t contain hyphens.

Hint: Regex Pattern Matching

-- Use NOT regexp_matches() to exclude hyphens
SELECT *
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE NOT regexp_matches(domain, '-')
ORDER BY price ASC;

Find Domains Without Numbers

Find domains that don’t contain any digits.

Hint: Regex for Digits

-- Use NOT regexp_matches() with '\d' pattern for digits
SELECT *
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE NOT regexp_matches(domain, '\d')
ORDER BY price ASC;

Final Challenge

Find Short and Cheap Domains

Combine the conditions from previous tasks to find short, cheap domains:

Your task: Find domains that are:

1. Less than 9 characters long
2. Under $100
3. Contain the ‘.co’ domain extension
4. Don’t have hyphens
5. Don’t have numbers
6. Order by price (cheapest first)

Hint: Combining Multiple Conditions

-- Combine all conditions with AND
SELECT domain, price, extensions_taken
FROM 'datasets/domain_prices/Namecheap_Market_Sales_Buy_Now.csv'
WHERE char_length(domain) < 10
  AND price < 100
  AND contains(domain, '.co')
  AND NOT regexp_matches(domain, '-')
  AND NOT regexp_matches(domain, '\d')
ORDER BY price ASC;

Now Write Your Own Query

Write your own query to find the best cheap and short domain names. Pick any price, domain length, and domain extension. Write down the top 3 domains you found.

Task 1.1 Summary

Congratulations! You’ve completed the domain analysis warmup. You’ve learned to:

✅ Load and explore CSV data with DuckDB
✅ Use string functions like char_length() and contains()
✅ Apply regex patterns with regexp_matches()
✅ Combine multiple conditions with AND and NOT
✅ Order results by price for best deals
✅ Analyze domain characteristics and pricing

Key Skills Developed:

• String Manipulation: Working with domain names and extensions
• Conditional Logic: Combining multiple filters
• Pattern Matching: Using regex for complex searches
• Data Analysis: Finding patterns in domain pricing

What You Found:

• Short, cheap domain names without hyphens or numbers
• The best deals on premium domain extensions
• Patterns in domain pricing and availability

Next Step: Continue with Task 1.2 to explore more advanced analytics techniques.