SQL + Python Week 1

Scenario

Imagine you are a Data Analyst at CloudSoft, a Software-as-a-Service (SaaS) company that provides cloud storage solutions to businesses. Your company has been experiencing a plateau in customer growth recently and is concerned about customer retention. The management is interested in understanding customer behavior, specifically, how often customers renew their subscriptions and which features are most widely used.

The database consists of three tables:

1. customers table: Information about each customer
   • customer_id
   • name
   • email
   • signup_date
2. subscriptions table: Subscription data including start and end dates
   • customer_id
   • plan_type
   • start_date
   • end_date
   • subscription_id
3. feature_usage table: Log of features used by each customer
   • usage_id
   • customer_id
   • feature_name
   • usage_date

Your task is to analyze this data to identify trends in customer retention and feature usage.

Challenge Questions

Access the challenge here.

1. How many customers are on each subscription plan?
2. How many customers have used the ‘Collaboration’ feature?
3. List customers who have not renewed their subscriptions.
4. Which month had the highest feature usage?
5. Which customers have not used any features after their first month of subscription?
6. How many days on average do customers take to use a feature after signing up?

Why learn to run SQL in python?

By learning to run SQL in Python, one can take advantage of Python’s capabilities in data analysis and visualization along with SQL’s strengths in data management. Opportunities ranging from web development to data science are made possible by this skill set. With a single, streamlined workflow, you can effectively query databases using SQL and then manipulate and visualize that data using Python. Your value in a variety of technical roles increases as a result of this integration, which increases your versatility and efficiency when handling tasks involving data.

Steps to run SQL code in python

Import SQLite: First, you’ll need to import SQLite to use it within your Python environment.

import sqlite3

Connect to a database: After that, connect to your SQLite database. SQLite will create a database for you if one doesn’t already exist.

conn = sqlite3.connect('mydatabase.db')

Create a cursor object: Using a cursor object, you can run SQL queries.

c = conn.cursor()

Fetch the data: We have already provided the code that created the database. You can execute your queries and print your results.

c.execute("SELECT * FROM employees")
print(c.fetchall())

Close the connection: Lastly, remember to cut off the database connection. You must do this for every challenge question.

conn.close()