Filtering Data with the Visual Query Builder

The Anatomy of a Question

In Metabase, any query, chart, or data exploration you create is called a "Question." This terminology reinforces the goal of data analysis: to ask questions of your data. The primary tool for non-SQL users is the visual query builder, which allows you to construct a query piece by piece.

Let's start by watching a very brief demonstration of the query builder in action. This will give you a quick visual orientation before we walk through the steps together.

Building Your First Question: "How many orders have we received per month?"

This is a classic descriptive analytics question. Let's use the visual query builder to answer it step-by-step.

1. Start a New Question and Pick Your Data

First, navigate to your Metabase homepage.

1. Click the + New button in the top right.
2. Select Question.
3. Metabase will ask you to pick your starting data. Choose the Sample Database, and from its list of tables, select the Orders table.

You should now see the query builder interface, with "Orders" selected as your data source. This is the starting point for every question.

2. Filtering the Data

Raw data is often more than you need. The first step in most analyses is to filter the data to the specific subset you're interested in. In SQL, this is your WHERE clause.

Let's refine our question to "How many orders have we received per month in the last three years?"

1. In the query builder, click the Filter button.
2. A dropdown will appear, listing all the columns in the Orders table. Select the Created At column.
3. Metabase is smart about date columns and will offer you a range of time-based filtering options. Select Previous and then enter 3 and choose Years from the dropdown.
4. Click Add filter.

You have now instructed Metabase to only consider records where the Created At date falls within the last three years.

3. Summarizing the Data

Now for the core of the analysis. We don't want to see every single order; we want to see an aggregated count grouped by month. In SQL, this would be a COUNT(*) with a GROUP BY clause. In Metabase, this is called a "summary."

A summary consists of two parts:

• The Metric: The number you want to calculate (e.g., a count of rows, a sum of a column, an average).
• The Grouping / Breakout: The dimension by which you want to slice your metric (e.g., by month, by product category, by user state).

Let's add our summary:

1. Click the Summarize button.
2. For the metric, the default Count of rows is exactly what we need.
3. Next to the metric, you'll see the option to add a grouping. Click Pick a column to group by.
4. Select Created At. Metabase will automatically group it by month, but you can click on "by month" to choose a different granularity (like day, week, or year). "Month" is perfect for our question.

The summary section should now read Count by Created At: Month.

4. Visualizing the Result

Now, click the blue Visualize button at the bottom left. Metabase will run the query you've constructed and, because you've grouped by a time series, it will automatically generate a line chart showing the count of orders for each month over the last three years.

Congratulations, you've just answered your first business question with Metabase!

Bridging the Gap: From Clicks to Code

As a developer, you understand that this user-friendly interface must be generating code behind the scenes. Metabase makes it easy to see the SQL that your visual query produces. This is an incredibly powerful feature for learning and for understanding what your team's tools are doing under the hood.

In the top-right corner of the query builder, you should see an icon that looks like a terminal or code editor, labeled "View the SQL". Click it.

You'll see a SQL query that contains SELECT, COUNT(*), FROM, GROUP BY, and ORDER BY clauses. Notice how the visual steps you took—picking a table, adding a filter, and defining a summary—map directly to these standard SQL statements. The DATE_TRUNC function is what Metabase uses to group the Created_At timestamp into monthly buckets.

This "View the SQL" feature is your bridge between the world of business intelligence tools and your own technical background.

For developers accustomed to writing SQL, the way Metabase separates "adding a column" from "summarizing" can be a new concept. The following reading clarifies this distinction.

Your Turn to Practice

Now it's your turn to build a question from scratch. Try to answer the following:

"What is the average order Subtotal, grouped by Product → Category?"

To do this, you will need to:

1. Start a new question.
2. This time, you will need to join data. When you pick your starting data (Orders table), look for the "Join data" button. Use it to join the Products table. Metabase will automatically suggest joining on Product ID.
3. In the Summarize section, change the metric from Count of rows to Average of... and select the Subtotal column from the Orders table.
4. For the grouping, select the Category column from the joined Products table.
5. Visualize the result. Metabase will likely choose a bar chart.

Take your time to work through it. Don't forget to use the "View the SQL" button to see how a JOIN and an AVG function are represented.

Conclusion

In this lesson, you made the leap from theory to practice by building your first analysis in Metabase. You learned how to translate a business question into a series of steps in the visual query builder, using filters and summaries to arrive at a meaningful answer.

Key Takeaways:

• A Metabase "Question" is built by chaining together steps: picking data, filtering, and summarizing.
• Filtering narrows down your data, analogous to a SQL WHERE clause.
• Summarizing aggregates your data, analogous to functions like COUNT, SUM, AVG combined with a GROUP BY clause.
• The "View the SQL" feature is a powerful tool that reveals the underlying code generated by the visual builder, connecting the UI to familiar SQL concepts.

You've now experienced the workflow of a non-technical analyst. In our next lesson, we will switch gears and use the native SQL editor. This will allow you to leverage your existing coding skills directly, while we begin to explore the specific types of SQL queries and patterns that are common in analytics but might be new to you.