Skip to content

About

Bike-share trip analysis (2019 to Q1 2020) in SQL and R comparing casual riders and members.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

54 Commits

Folders and files

NameName
Last commit message
Last commit date
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Cyclistic Bike-Share Analysis

Project Summary

This project analyzes historical Cyclistic bike-share trip data to compare how casual riders and annual members use the service. The goal is to identify behavioral patterns that could help inform marketing strategies aimed at increasing annual memberships.

Business Question

How do casual riders and annual members differ in their bike-share usage, and what patterns could support member conversion strategies?

Tools Used

  • SQL: data cleaning, transformation, and analysis
  • R: analysis support, validation, and summary work
  • R Markdown: reporting and project presentation

Workflow

This project uses a dual workflow in SQL and R. The main cleaning and analysis process was completed in SQL, then reproduced in R to confirm the approach and demonstrate the same analytical logic across two environments.

Repository Contents

1. SQL Cleaning

Location: Analysis/Scripts/SQL/01_cleaning/

This folder contains the quarterly cleaning scripts used to standardize the raw trip data:

Main tasks:

  • standardized column names and formats
  • cleaned inconsistent or incomplete records
  • prepared each quarter for merging

2. SQL Merge

Location: Analysis/Scripts/SQL/02_merge/merge_cleaned_tables.sql

This script combines the cleaned quarterly datasets into a single table for analysis.

3. SQL Analysis

Location: Analysis/Scripts/SQL/03_statistical_analysis/

This folder contains SQL queries used to analyze rider behavior:

The analysis focuses on:

  • ride duration
  • member versus casual usage
  • time-of-day behavior
  • monthly ride trends

4. R Workflow

Location: Analysis/Scripts/R/cyclistic_workflow.R

This script supports the analysis by reproducing the workflow in R and summarizing key patterns.

Key Findings

  • Casual riders generally take longer rides than annual members.
  • Casual rider activity is higher on weekends and during warmer periods.
  • Members use the service more consistently on weekdays.
  • Usage patterns suggest different purposes for each rider type.

Reports

Data Source

The analysis uses publicly available Cyclistic (Divvy) trip data. Raw source files are not included in this repository due to size constraints. The SQL scripts assume the original data has already been loaded into a database environment.

Limitations

This analysis is based on trip history data only. It does not include demographic information or direct evidence of rider motivation, so interpretations about commuting or leisure behavior should be treated as informed usage patterns rather than confirmed intent.

Notes

The analysis, the SQL/R code and the conclusions were done by hand, without AI coding tools.

About

Bike-share trip analysis (2019 to Q1 2020) in SQL and R comparing casual riders and members.

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages