Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 33 ยท Projects

Project: Analyzing a CSV Dataset

Import a CSV into SQLite, clean messy text into typed columns, then answer questions with aggregations, top-N per group and cohort retention queries.

SQLite is a surprisingly good data analysis tool. A spreadsheet starts to struggle at a few hundred thousand rows; SQLite handles millions without complaint, on a laptop, with no server to install. The usual workflow is simple: import a CSV file into a table, clean it, and then ask questions in SQL. It is repeatable (the queries are a script you can re-run), exact, and fast.

In this project you follow that workflow from raw text to answers. The sqlite3 shell import step cannot run in a browser, so it is shown read-only. Everything after it runs live on data created inside each example: a messy "staging" import to clean, and then a 120-order dataset to analyze with monthly totals, top-N queries and a cohort table. You will use functions, joins and CTEs, window functions and dates.

From file to answer

Import CSV into a text-only staging table.
Clean Trim, convert, validate, dedupe.
Analyze Group, rank, compare.
Export CSV or a new table.

Keep the raw staging table; never edit the original file.

1 / 2

Step 1: import the CSV (read-only)

Suppose orders.csv has a header row and the columns order_id, order_date, customer, country, product, quantity, price. In the sqlite3 shell you switch to CSV mode and import into a table. If the table does not exist, the shell creates it using the header row for the column names, and every column is TEXT, because a CSV file has no types.

TEXT
$ sqlite3 shop.db
sqlite> .mode csv
sqlite> .import orders.csv staging
sqlite> .schema staging
CREATE TABLE IF NOT EXISTS "staging"(
  "order_id" TEXT, "order_date" TEXT, "customer" TEXT, "country" TEXT,
  "product" TEXT, "quantity" TEXT, "price" TEXT
);
sqlite> SELECT count(*) FROM staging;

Useful variations: .import --skip 1 orders.csv staging skips a header when you created the table yourself first (for example with proper types), and .headers on with .mode column makes query output readable. You can also run it non-interactively with sqlite3 shop.db ".mode csv" ".import orders.csv staging". Exporting is the mirror image: .mode csv, .output result.csv, run a query, then .output stdout.

Importing everything as text is a feature, not a flaw. Nothing is lost or silently changed, so the next step can decide how to handle every odd value. See Running SQLite: Browser and the sqlite3 Shell for more about dot-commands.

Step 2: clean the staging table

Real exports are messy: stray spaces, inconsistent capitalization, dates written two ways, currency symbols, thousands separators, blanks where a value is missing and the same row twice. The pattern is to create a properly typed, constrained table and fill it from the staging table with one INSERT ... SELECT that fixes everything. Rows that cannot be fixed are skipped by INSERT OR IGNORE because they break a NOT NULL or PRIMARY KEY rule, and a follow-up query lists them so a human can look.

SQL Clean a messy staging table into a typed table

Press Run (or Ctrl + Enter) to see the result here.

Eight raw rows become five clean ones. 1003 appears twice but is stored once, because the second INSERT OR IGNORE hits the primary key. 1005 is dropped for its unreadable date and 1006 for its missing price, and the last query lists both with the reason. Look at what each conversion did:

  • trim() removed the stray spaces around names, and upper() plus the CASE merged us, US and USA into one country code.
  • The GLOB patterns recognised two date layouts, and the second was rearranged from day/month/year to ISO order with substr. Text that matched neither became NULL and was rejected by NOT NULL.
  • The blank quantity on order 1003 became 1 through nullif and coalesce, a business decision you should make consciously.
  • The $ and the thousands comma were removed with replace, and the price converted to integer cents ('1,299.00' became 129900), exactly as in Project: Personal Budget Tracker.

Keep staging after cleaning. If you later find a mistake in the rules, you can empty orders and run the INSERT ... SELECT again, with no need to import the file again. Drop staging only when the data is final.

Step 3: first questions

For the analysis, the next examples use a clean orders table of 120 orders from January to June 2026, generated inside the example with a recursive CTE so it is deterministic. Order totals are quantity * price_cents. Start with the overall shape and the month-by-month trend.

SQL Revenue by month and product share

Press Run (or Ctrl + Enter) to see the result here.

The overview reports 120 orders from 16 customers between 2 January and 30 June. Revenue is steady at roughly 1,400 to 1,750 per month with the best month being March. The product ranking holds a lesson: the Desk sells the same number of units as the Gadget and Widget, but because it costs far more it brings about 65% of all revenue. The share column uses sum(sum(...)) OVER (), a window function applied on top of the grouped result, to get the grand total in the same query.

Step 4: top-N per group and cohorts

"Top 5 overall" is just ORDER BY ... LIMIT. "Top 2 per country" needs a window function, because LIMIT cannot work per group. Rank inside each partition, then keep the first ranks. Add a unique tie-breaker (here customer_id) so equal totals always rank the same way.

A cohort analysis groups customers by the month of their first order and then counts how many of each group were active in the following months. It answers the question every business asks: do customers come back? It combines a CTE for each customer's first order with a month-difference calculation.

SQL Top customers per country and cohort retention

Press Run (or Ctrl + Enter) to see the result here.

The ranking shows each country's top two spenders, for instance customer 1 leading Germany with 1,833.29. In the cohort table, each row is a start month, month0 is the size of the cohort and the other columns count customers still ordering later. The January cohort has 5 customers and all five order again in every one of the next three months; the March cohort starts with 4 and keeps 3 by month 2. Zeros at the bottom right (the June row has nothing after month0) are not lost customers: the data ends in June, so later months simply have not happened yet. Always say which cells are unobservable when you present cohort tables.

To turn counts into percentages, divide each monthN by month0. To save a result for a chart or another tool, wrap the query in CREATE TABLE report AS SELECT ... or export it with .mode csv and .output in the shell.

Habits that keep analysis trustworthy

  • Check counts at every step. Compare the rows imported, cleaned and rejected. If 8 went in and 5 came out, you should know exactly where the other 3 went.
  • Keep money in integers and dates in ISO format. It makes sums exact and sorting correct.
  • Write the queries into a file. A .sql script plus the raw CSV reproduces the whole analysis; run it with sqlite3 shop.db < analysis.sql.
  • Index only after loading. Bulk-load first (inside one transaction), then CREATE INDEX on the columns you group or filter by, and check plans with EXPLAIN QUERY PLAN (see EXPLAIN QUERY PLAN and the Optimizer).
  • Be suspicious of averages. Look at the distribution (min, max, counts per bucket) before trusting a mean.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. When you .import a CSV into a table that does not exist yet, what are the column types?

    The shell creates the table from the header row with TEXT columns. You convert to proper types in a cleaning step.
  2. 2. Why keep the raw staging table after cleaning?

    The staging table is the untouched source of truth inside the database, so the cleaning step can be repeated.
  3. 3. What does INSERT OR IGNORE do to a staged row whose cleaned date is NULL and the column is NOT NULL?

    OR IGNORE skips rows that violate NOT NULL, UNIQUE, PRIMARY KEY or CHECK constraints, so bad rows are left out and the rest load.
  4. 4. How do you get the top 2 customers per country?

    LIMIT applies to the whole result. A window function ranks inside each partition, and a filter on the rank keeps the top rows of every group.
  5. 5. In a cohort table, why is the bottom-right corner full of zeros?

    Recent cohorts have had less time to come back. Cells beyond the end of the data are unknown, not zero activity.

Summary

  • Import CSV files into an all-text staging table with .mode csv and .import; the shell import is read-only here.
  • Clean with one INSERT ... SELECT using trim, upper, GLOB, substr, replace, nullif, coalesce and CAST, into a typed table with constraints.
  • INSERT OR IGNORE skips rows that break constraints; list the rejected rows afterwards with an anti-join.
  • Use GROUP BY with strftime for time series, sum(sum(...)) OVER () for shares, and row_number() OVER (PARTITION BY ...) for top-N per group.
  • Cohort tables combine each customer's first-order month with a month index; be careful reading cells that are still in the future.

Next: SQLite Exercises with Solutions, practice across the whole track.