Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 39 ยท Projects and Practice

Time-series SQL Practice Set

Practise the core time-series queries with graded exercises: windows, gaps, latest values, rates, moving averages and top-N per group. Runnable solutions included.

Time-series work uses a small set of query shapes again and again. If you can write each of them without looking anything up, dashboards, alerts and reports become routine. This practice set drills those shapes on one shared dataset. Each exercise states a goal, you try it first in the editor, and the TryIt holds a worked solution you can compare with.

The queries are portable SQL, which runs here and carries over to InfluxDB 3 SQL (replace strftime/integer division with date_bin, and datetime(...) with plain timestamps). In InfluxDB 2.x the same ideas are Flux pipelines: aggregateWindow, last, derivative, movingAverage, top. You can review the concepts in aggregate windows, checks and schema design.

The dataset

Every exercise below starts with the same setup, repeated so each editor is self-contained: three hosts, a reading every 5 minutes for 3 hours (36 readings each, so 108 rows), with a cpu percentage and a monotonically increasing bytes_total counter. host c loses its counter at one point (a restart) and misses some readings.

Exercise 1: latest value per host

Goal: one row per host with its most recent CPU reading and the time of that reading.

SQL Exercise 1: latest per host

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

In Flux: |> group(columns: ["host"]) |> last().

Exercise 2: 30-minute means with a count

Goal: for each host and 30-minute window, show the mean CPU and how many readings it contains. Which windows are incomplete? (A complete window has 6 readings.)

SQL Exercise 2: windows and completeness

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

Only host c has incomplete windows, caused by the missing readings. Dashboards should show these windows differently, or alert with a deadman check.

Exercise 3: per-second rate from a counter, with resets

Goal: bytes per second between consecutive readings of host c, handling the restart.

SQL Exercise 3: counter rate

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

The output shows the row after the gap (a longer interval: the rate is still right because we divide by real elapsed time), the reset row (the increase is the new value, not a negative number), and a few ordinary rows. This is Flux's derivative(nonNegative: true).

Exercise 4: top reading per host per hour

Goal: for each host and hour, find the timestamp and value of the highest CPU.

SQL Exercise 4: top-1 per group

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

The tie-breaker ts in the ORDER BY makes the result deterministic.

Exercise 5: how long was each host above a threshold?

Goal: for each host, the percentage of readings above 70 percent and the longest run of consecutive readings above 70.

SQL Exercise 5: time above threshold and longest run

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

The "gaps and islands" trick, subtracting two row numbers, is the standard way to find consecutive runs, and it is how "above threshold for 15 minutes" conditions are evaluated.

Exercise 6: design questions (no code)

  1. A fleet reports cpu for 5,000 hosts every 10 seconds. How many points per day? (5,000 x 8,640 = 43.2 million.) What retention and rollups would you choose, and why?
  2. Someone proposes session_id as a tag. List two reasons to say no (cardinality).
  3. A dashboard over 90 days times out. List three fixes (dashboards, downsampling).
  4. Writes are slow at 200 requests per second of one point each. What do you change (batching)?

Write your answers down, then compare: raw data kept briefly plus hourly and daily rollups; unbounded value, index growth, queries touching millions of series; query a downsampled bucket, use windowPeriod, reduce series; batch thousands of points per request.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which technique finds consecutive runs of rows meeting a condition?

    Subtracting a per-group row number from the overall row number gives a constant per consecutive run.
  2. 2. Why include ts in the ORDER BY of a top-1 query?

    A tie-breaker keeps results stable when values repeat.
  3. 3. What gives the rate of a counter that reset?

    After a reset the increase since the restart is the new value.
  4. 4. How many points per day come from 5,000 hosts each reporting one value every 10 seconds?

    5,000 x 8,640 = 43,200,000.

Summary

  • Latest-per-group, window means with counts, counter rates, top-N per group and runs above a threshold cover most time-series reports.
  • Always include completeness (counts) and deterministic tie-breakers.
  • Handle counter resets and gaps explicitly.
  • The same shapes exist in Flux and InfluxDB 3 SQL.

Next: Troubleshooting InfluxDB