Querying time series data with SQL: examples
Tags: beginners, sql, database, opensource
Published: 2021-03-01 20:47:53

(*This content is based on a blogpost originally published in [Crate.io](https://crate.io/a/time-series-cratedb-cloud-sql-examples/)*)

When [signing up for CrateDB Cloud](https://go.cratedb.com/lp-free-trial.html?utm_source=startpage&utm_medium=website&utm_campaign=free-trial), you can import the [NYC taxi dataset](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) from the console with only a couple of clicks. We like this dataset because it includes information from tens of millions of taxi trips, with multiple data types—including time-series data. 

In a previous post, I showed you [how to load the NYC taxi dataset](https://dev.to/crate/try-cratedb-cloud-for-free-2an5) into CrateDB Cloud. Now, I will give you **some SQL queries you can try.** This post will be especially useful if you are (like me) still a beginner in the world of SQL. 🤓

Let's get into it. 

Assuming that you already have the full dataset imported, you now have about 40 million data records in your cluster. Let's start by taking a look at our table:

```sql
SELECT * FROM nyc_taxi
LIMIT 10;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pqrvlbbvbz6n3ceezpw1.png)

If you scroll to the right, you will see all the information in your table: fare amounts, passenger numbers, tips and tolls paid...

To play around with your data, try the following queries:


### 1. How many rides happened per day during July 2019?

The SQL query:

```sql
SELECT
  date_trunc('day', pickup_datetime) AS day,
  COUNT(*) AS rides
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY 1
ORDER BY 1 LIMIT 100;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wd7xmpecva4k7vk2jmbf.png)

### 2. What is the total distance traveled during July 2019, as well as the average distance per trip?

The SQL query:

```sql
SELECT
  date_trunc('day', pickup_datetime) AS DAY,
  COUNT(*) AS rides,
  SUM(trip_distance) AS total_distance,
  SUM(trip_distance) / COUNT(*) AS average_per_ride
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY DAY
ORDER BY 1 LIMIT 100;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y067b8yivm2fl7kde6oo.png)


### 3. What are the busiest hours of the day?

The SQL query:

```sql
SELECT
  EXTRACT(HOUR FROM pickup_datetime) AS HOUR,
  COUNT(*),
  SUM(trip_distance) AS total_distance
FROM nyc_taxi
WHERE pickup_datetime BETWEEN '2019-07-01T00:00:00' AND '2019-07-31T23:59:59'
GROUP BY HOUR
ORDER BY COUNT(*) DESC LIMIT 100;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gstz11j8hpcs613r6hvj.png)

### 4. How many unique pickup locations there are in the entire dataset?

With this one, things start getting a little bit more interesting. 

For this query, you would normally do something like this:

```sql
SELECT COUNT(DISTINCT(pulocationid)) FROM nyc_taxi;
```

However, the amount of data is too large. If you try this, CrateDB Cloud will immediately trip a circuit breaker: this query can't be performed reasonably, without having to do a ridiculous full scan of the table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r8brtewy26i9t76cbo59.png)

The solution is to use a function called `HYPERLOGLOG`:


```sql
SELECT HYPERLOGLOG_DISTINCT(pulocationid) FROM nyc_taxi;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z8rk25yebvbueg94tj3i.png)

This function uses an algorithm to estimate the number of data records, and it is fast even on very large datasets. You can use HYPERLOGLOG_DISTINCT() just like you would use COUNT(DISTINCT) in your queries.

### 5.  Which are the most high-grossing location pairs in NYC for the entire year? For each pair, how much more it grosses if compared to the next best one?

To end, we get advanced for real: this is an example of how to use window functions.  

In this case, the query uses the `LAG()` function:

```sql
SELECT pulocationid, dolocationid, cnt, total, next_highest_grossing, total - next_highest_grossing AS leads_by
FROM (
  SELECT
    pulocationid,
    dolocationid,
    COUNT(*) AS cnt,
    SUM(total_amount) AS total,
    LAG(sum(total_amount), 1) OVER (ORDER BY SUM(total_amount)) AS     next_highest_grossing
  FROM nyc_taxi
  WHERE pickup_datetime BETWEEN '2019-01-01T00:00:00' AND
  '2019-12-31T23:59:59'
  GROUP BY pulocationid, dolocationid
  ORDER BY total DESC
  LIMIT 100
) AS sub;
```

The result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9nn1101tc7ofi66lgjgz.png)

Hope this was useful! See you next time 👋 

_______________________________________________________________

*There's nothing better than trying things by yourself! [Download CrateDB](https://crate.io/download/) or [sign up for a CrateDB Cloud free trial](https://go.cratedb.com/lp-free-trial.html?utm_source=cloudproductpage&utm_medium=website&utm_campaign=free-trial). Experiment... And tell us what you think* 😁

*Apart from [Dev.to](https://dev.to/crate), you can reach to the [Crate.io](https://crate.io) team in:*

* *[Github](https://github.com/crate) (We'd love a* ⭐️ 🙏*)*
* *[Twitter](https://twitter.com/crateio)*
* *[Our community page](https://community.crate.io)*

*See you around* 🐐