Lesson 1 - Learning to Build Reports with Wink and SQL

A step-by-step peek under the hood of Wink Reports to see how the designer generates SQL queries for your reports.

Welcome to lesson 1 of the "Wink Reports for Data Analysts" course. In this series of lessons, we will be going over Wink Reports and assume that you are confident with Excel, including formulas, and preferably with SQL. We won't be covering the basics, such as how formulas or joins work. Instead, we'll be mapping the concepts from SQL and advanced spreadsheets to how they work in Wink Reports.

This lesson will cover how Wink Reports work under the hood, and we'll build a complex report that requires data from multiple sources and uses complex filtering and window functions.

We generate SQL behind the scenes, and I will show the query from the data source in the video. You can modify the columns and add formulas, although we don't allow you to write your own custom SQL. Our product is meant for clients who are more comfortable with an Excel view of life and can easily get tabular data ready. However, if you're already familiar with SQL, seeing how this works will make the interface make more sense, and you'll know how to approach more complex problems.

To begin, we'll start with a subquery to pull data from a sales table. We'll use functions like UPPER() and IFNULL() to transform the data as needed and see how it translates into SQL. We'll also add a filter to show only data for a specific date range.

Next, we'll learn about aggregation and grouping. We'll use functions like SUM() and COUNT() to aggregate data and group it by specific columns like customer name or date. We'll also learn how to map data to a specific time period, like monthly sales.

After that, we'll dive into joining data from multiple sources. We'll use left joins, inner joins, and unions to combine data from different tables. We'll also learn how to rename columns and use them as keys to join data.

Finally, we'll learn how to merge multiple reports into a single report using a merge report data source. We'll see how to select which data sources to include in the merge, how to set join types, and how to add filters that apply after the merge.

Throughout the lesson, we'll see how Wink simplifies the process of building SQL queries and reports. We'll see how to save and reuse queries as data sources, how to create formula columns and apply filters, and how to use the visual report builder to create reports without writing SQL.

By the end of this lesson, you'll have a solid understanding of how to create reports using SQL and Wink. You'll be able to pull data from multiple sources, transform and aggregate it, and merge it into a single report. You'll also have a good grasp of the core SQL concepts that underlie report building.

Topics with time stamps:

• Introduction and agenda (00:00)
• What is SQL? (01:20)
• What is Wink? (02:28)
• Getting started with Wink (03:16)
• Using the Query Designer to build a subquery (04:40)
• Creating a formula column (08:02)
• Filtering results (10:23)
• Modifying the subquery to create different SQL formulas (13:24)
• Using aggregation functions (15:42)
• Doing joins (18:17)
• Merging reports (21:23)
• Sorting columns (27:14)
• Conclusion and next steps (27:55)

Top learnings from this lesson:

1. Wink is a platform for building data reports that uses SQL-like queries to generate data visualizations.
2. Wink uses subqueries to pull data from different data sources, and you can modify the data using formulas and functions which are translated into SQL.
3. You can use aggregation functions like COUNT and SUM to manipulate data in Wink.
4. Wink also supports joining data from different data sources using SQL joins.
5. Filters in Wink can be applied both before and after merging data from different sources.
6. Wink allows you to save data reports as data sources, which you can then use in other reports.
7. The next lesson will cover how to plan and build a large report using Wink.

Pop quiz!

1. What is the purpose of Wink?
2. How can you modify the select statement in Wink?
3. What is a group by statement used for in SQL?
4. What is a left join in SQL?
5. How can you turn a merged report in Wink into a data source for future reports?

Answers:

1. Wink is a tool for creating SQL reports and visualizations.
2. The select statement in Wink can be modified by adding formulas, filters, or new columns.
3. A group by statement is used to group results based on a specified column or set of columns, allowing for aggregation and summary statistics.
4. A left join is a type of SQL join where all rows from the left table are included in the result set, along with any matching rows from the right table.
5. A merged report in Wink can be turned into a data source for future reports by turning off draft mode and saving it as a report, which can then be used in other reports.