Data Cleaning and SQL Analysis

Data Cleaning

Start with a data profile: for each column, record its type, number of missing values, number of unique values, and the minimum and maximum. Then look for the usual problems: duplicates, missing values, inconsistent text, wrong data types, impossible values (negative quantities, end time before start time) and outliers.

Clean in whichever tool suits the data: Power Query or Excel for small files, SQL for data already in a database, pandas for large or repeated work. Whatever the tool, keep a cleaning log:

Save the cleaned data in data/clean. Your final row count, and what was removed, goes into your documentation.

SQL Analysis

Load the clean data into your database and answer your SMART questions with SQL. Work question by question:

1. Write the question as a comment at the top of the query.
2. Start simple (one table, a COUNT) and check the result makes sense.
3. Add filters, grouping and joins step by step.
4. Save each query as its own file in the sql folder, numbered in order (01_rides_by_type.sql).
5. Export the result to CSV or copy it into a results table.

Typical project queries: totals and averages by group, trends by month, top and bottom 10 lists, comparisons between segments, and percentages of the total. A useful pattern for percentages:

SELECT rider_type,
       COUNT(*) AS rides,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM rides), 1) AS pct_of_rides
FROM rides
GROUP BY rider_type;