example-data-and-queries.md

Remote taxi data

Get the total monthly dollar amount for each month of 2023

SELECT 
  DATE_TRUNC('month', tpep_pickup_datetime::TIMESTAMP) AS pickup_month, 
  SUM(total_amount) as daily_total
FROM 
  nyc_taxi.taxi_single
WHERE 
  date_part('year', tpep_pickup_datetime::TIMESTAMP) = 2023
GROUP BY ALL
ORDER BY
  pickup_month

Get the average trip distance per day

SELECT
  CAST(tpep_pickup_datetime::TIMESTAMP AS DATE) as pickup_day,
  AVG(trip_distance) AS average_distance
FROM
  nyc_taxi.taxi_md
GROUP BY
  pickup_day
ORDER BY
  pickup_day

For the Stripe data below

Get total dollar amount sold each month

To change the number of months, change the WHERE clause.

• Good for; bar chart and line chart with single line

SELECT
  strftime(CAST(transaction_date AS DATE), '%Y-%m') AS formattedDate,
  SUM(transaction_amount) AS total_amount
FROM
  local.stripe
WHERE
  transaction_date >= '2023-01-01' AND
  transaction_date <= '2023-09-31'
GROUP BY ALL
ORDER BY
  formattedDate;

Get total dollar amount sold of each product_category for each month

To change the number of months, change the WHERE clause.

• Good for; stacked bar chart and line chart with multiple lines

SELECT
  strftime(CAST(transaction_date AS DATE), '%Y-%m') AS formattedDate,
  product_category,
  SUM(transaction_amount) AS total_amount
FROM
  local.stripe
WHERE
  transaction_date >= '2023-01-01' AND
  transaction_date <= '2023-03-31'
GROUP BY ALL
ORDER BY
  formattedDate, product_category;

mock-stripe.csv