I’m a software engineer at [Ternary](https://ternary.app/), a company that provides a FinOps platform to serve Google Cloud (GCP) customers' unique needs. Ternary’s platform empowers cloud engineers, IT finance, and business teams to collaborate more effectively on public cloud cost optimization. The company is in high-growth mode on all fronts, and its platform now supports many of the largest GCP customers. These customers bring the challenge of providing a SaaS platform at scale. 
 
Below, I show how Ternary has solved the challenge of managing the large volume of cost-related data these customers produce, utilizing that data to forecast and optimize spend. I explain the so-called 3-query approach to the analysis of large multidimensional datasets that we use to identify the most interesting data for our users and provide real-world code snippets in the second half of this article.

## Multidimensional analysis of cost datasets

![Ternary dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-dashboard.png)

Ternary’s platform features the ability to break down costs by projects and other dimensions across a time series. For users that have many values in a given dimension (e.g., a few thousand GCP projects), or for those who want to analyze unique combinations of dimensions, we would have to load and analyze substantial result sets on the front-end, not to mention that we would also frequently run into issues with Cube’s response limit of [50,000 rows](https://cube.dev/docs/query-format#query-properties). For example, let’s say I want to look at total spend for every project across the last 30 days while breaking that spend by one or more additional dimensions. Chances are that the result set would contain thousands of rows and if I exceed the limit of 50K rows, I will generate an incomplete dataset and won’t be able to calculate my total costs. 

Ternary is a cost-management platform, so it’s critical that we present complete, accurate data. To enable our users to perform multidimensional analysis of vast volumes of cost data, we asked ourselves what data our users most want to see.

Ternary displays most of its data in line or bar graphs, which are useful only with data points up to a certain amount. A line graph with 50 increments on the y-axis can be hard to read, especially if some y-axis values are much greater than others. At Ternary, we hypothesized that many of our key users would want to focus on what we call, informally, their “most interesting” data points, which we defined as their highest spend in a 30-day period, as well as the general shape of their expenses during that time. It’s debatable what “most interesting” is in every case. But for us, it is usually the groupings that account for the most spend.

Now that we have assumed what our users are really interested in, we imposed limits on our queries so that the user sees only that data. This focus makes our charts more readable and removes the concern about maxing out the Cube response limit.

In limiting the data, we inevitably lose some portion of the entire picture. The top 10 “most interesting” dimension values will likely not account for all of a user’s costs. If there were as many as 100 dimension values, our method would expose but a fraction of these. 

So, to account for any missing data, we roll all the “less interesting” data points into an “other” column. By including this “other” column, we fulfill our above-mentioned mandate to provide complete and accurate data, reflecting total spend and ensuring the accuracy of any subsequent calculations. 

## The 3-query approach to querying top N rows

In order to expose the “most important” data and generate the “other” column, we need to fire off at least three queries. Let’s go through the high-level query breakdown here.

**Scenario:** As a Ternary user, I want to see how much my team has spent day by day, broken down by project ID and category, over the last 30 days.

**Query 1:** Request all of the cost totals day by day, over the last 30 days, with desired query filters. At this point, we will not include dimensions. Note that the “cost” measure in this example is of type “sum.”

![Query 1 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-1.png)

**Query 2:** Request the top N number of the “most interesting” combinations of projects and categories. In this case, we will find out which project-category pairs had the highest overall spend in the last month. For this scenario, let’s assume N is 3. 

![Query 2 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-2.png)

Now that we have a list of the top three combinations, we’ll generate query filters. Note that the new filters must use [Boolean logical operators](https://cube.dev/docs/query-format#boolean-logical-operators) to accurately filter for specific project ID-category storage groupings.

**Query 3:** Request cost day by day over the last 30 days, broken down by project-category pairs, using the filters generated from query two in addition to any user-selected filters. (Note that we will include additional dimensions now.)

![Query 3 image](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-query-3.png)

This query would return 3 * 30 = 90 data points. However, there is a discrepancy between the total spend of those three projects and the total overall cost on any given day. To account for this difference, we take the totals from query 1 and subtract the values from query 3 to generate an “other” column, which we also display for the user.

![Final chart](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/Ternary-final-chart.png)

By this method, we generate a complete overview, identifying the most expensive projects while preserving the total cost using the “other” column. While the overview does not display every project and some specificity is lost, all the data is reflected in one form or another. 

Keep in mind that N can be much larger than three; it should change according to the user’s goals. Lastly, it’s important to acknowledge that while this solution is a “three-step process,” the actual number of queries being run will be 1 + 2m, where m is the number of measures that the user wants to see. This is because we want to gather a separate set of filters for every measure requested. 

For example, say we wanted to see separately the three projects responsible for the most spend and the three projects responsible for the most usage. While we can use query 1 to request all of the selected measure totals across the time series, we will need to fire off queries 2 and 3 for both cost and usage separately. Otherwise, we will muddle the results with an inconsistent number of data points for cost and usage.

To try this out yourself, create the following Cube schema:

```js
cube(`CostByProjectAndCategory`, {
  title: "CostByProjectAndCategory",
  sql: `
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 14 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 15 AS cost, "production" AS project, "Network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 16 AS cost, "production" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 15 AS cost, "production" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 15 AS cost, "production" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 12 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 12 AS cost, "staging" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 13 AS cost, "staging" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 15 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 13 AS cost, "staging" AS project,"network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 13 AS cost, "staging" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 8 AS cost, "dev" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 9 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 8 AS cost, "dev" AS project,  "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 8 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 9 AS cost, "dev" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 8 AS cost, "dev" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 9 AS cost, "dev" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-07") AS timestamp, 7 AS cost, "test" AS project, "analytics" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-08") AS timestamp, 6 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-09") AS timestamp, 7 AS cost, "test" AS project, "storage" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-10") AS timestamp, 6 AS cost, "test" AS project, "databases" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-11") AS timestamp, 4 AS cost, "test" AS project, "network" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-12") AS timestamp, 6 AS cost, "test" AS project, "compute" AS category UNION ALL
    SELECT TIMESTAMP("2022-04-13") AS timestamp, 6 AS cost, "test" AS project, "analytics" AS category
  `,

  measures: {
    cost: {
      sql: "cost",
      type: "sum",
    },
  },

  dimensions: {
    timestamp: {
      sql: "timestamp",
      type: "time",
    },
    project: {
      sql: "project",
      type: "string",
    },
    category: {
      sql: "category",
      type: "string",
    },
  },
});
 
```

The three queries you’ll need to run are shown below, in JSON query format:

**Query 1**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ]
}
```

**Result**

![Query 1 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-1-result.png)

**Query 2**

```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp"
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "limit": 2
}
```

**Result**

![Query 2 result 1](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-2-result.png)

**Query 3**
```json
{
  "measures": [
    "CostByProjectAndCategory.cost"
  ],
  "dimensions": [
    "CostByProjectAndCategory.project",
    "CostByProjectAndCategory.category"
  ],
  "timeDimensions": [ {
    "dimension": "CostByProjectAndCategory.timestamp",
    "granularity": "day",
    "dateRange": [
      "2022-04-07",
      "2022-04-13"
    ]
  } ],
  "order": {
    "CostByProjectAndCategory.cost": "desc"
  },
  "filters": [ {
    "or": [
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["production"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["compute"]
          }
        ]
      },
      {
        "and": [
          {
            "member": "CostByProjectAndCategory.project",
            "operator": "equals",
            "values": ["staging"]
          },
          {
            "member": "CostByProjectAndCategory.category",
            "operator": "equals",
            "values": ["databases"]
          }
        ]
      }
    ]
  } ],
  "limit": 50000
}
```

**Result**

![Query 3 result](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/query-3-result.png)

### Performance

Because including a large number of dimensions complicates the Boolean logical filters applied, and because including a large number of measures increases the number of queries to which we must apply that complicated logic, it’s crucial to consider actual use cases and their resulting user impact. To test Ternary’s new query format, we used the Ternary Reporting Engine: a feature of our platform that allows users to query their data flexibly, by selecting different dimensions, measures, and filters. Here, we ran a number of queries with multiple measures, dimensions, and filters, to get a sense of how complex our queries could be without significantly compromising the app’s performance. We found that, using this 3-query approach, the duration of a request where N = 20 was the same as that of using one query to gather all the data, with the added benefit that our results stayed within the 50K-row limit.

### Cost

Given that Ternary is a cost-management platform, we recognize the importance of considering costs when making changes such as this one. As stated before, the number of Cube queries needed to fulfill a single request on behalf of any given customer would increase, from 1 to 1 + 2m. It follows that the cost to implement a feature in this way could also increase by a similar factor if, for example, using [BigQuery’s on-demand pricing](https://cloud.google.com/bigquery/pricing#on_demand_pricing) like we do, where the amount you are charged is proportional to the amount of data you read. Even if we were to increase the amount we spend, serving this data by a factor of, on average, three, five, or even ten, we determined that it would be a reasonable price to pay for the benefits, including showing customer data that conveys the full picture instead of truncating it as we previously had. 

Each use case is different and should be considered individually. If all queries are accelerated with [pre-aggregations](https://cube.dev/docs/caching#pre-aggregations) or if Cube points toward a Postgres instance, and if the query volume is relatively low, cost considerations may not be as relevant.

Katie Law

User Stories

How Ternary zeroes in on most interesting data.

Ternary's three-query approach to present the most interesting data in less than 50k rows

Previously, we’ve discussed the recent emergence of headless BI, and reviewed [headless BI’s four essential components][1]. Now, let’s take a more in-depth look at one of those layers: data modeling. Here is an introduction to open source data modeling in Cube’s headless BI platform.

How data is modeled is the first fundamental element of the business intelligence stack, because it organizes and streamlines querying a data source. Additionally, the data model defines the relationships between each data entity, which forms the basis of all analysis of that data.

Data modeling is a vital component of the BI stack and must be consistent and universally accessible. And so, its logic should be defined only once, and should be defined in code. Furthermore, this code should be governed by version control, to enable safe iteration as an organization grows.

## A data modeling language for Headless BI

![open source data modeling language for headless BI][image01]

Cube’s entire headless BI platform is open source—and this includes our open source data modeling language. This language is used to describe measures, dimensions, and relationships in a data source. Then, Cube servers use the model written in this language to generate SQL queries against data sources when responding to requests made to our REST, GraphQL, and SQL APIs.

Unlike [other data modeling languages][7], Cube’s data modeling language is not tied to any specific interface, visualization, or user experience. This decoupling makes it possible to use the same model definitions across every application. Each data consumer can have a custom-tailored experience in the data application of their choice while still working with consistent and standardized definitions.

## The design of Cube’s data modeling language

**Cube’s data model is a relational graph of the entities that contain measures and dimensions.** 

In Cube, entities are called “cubes”—inspired by OLAP cubes. In essence, they are data tables with a semantic meta layer that describes both the cube’s measures and dimensions and its relationships to other cubes. 


![open source data modeling - Cube][image02]

**Cube operates on the foundational primitives of *dimensions* and *measures*.** Users can define metrics by combining these primitives.  

***Dimensions***  are properties of entity objects, e.g., a user’s country or a product’s category. 

```javascript
cube(`Products`, {
  dimensions: {
    category: {
      sql: `category`,
      type: `string`
    }
  }
});
```

***Measures*** are aggregations, typically defined as SQL expressions or algebraic operations over other measures:


```javascript
cube(`Orders`, {
  measures: {
    totalValue: {
      sql: `value`,
      type: `sum`
    }
  }
});
```

Finally, cubes are connected to each other and constitute a data graph.


```javascript
cube(`Orders`, {
  joins: {
    Products: {
      relationship: `belongsTo`
      sql: `${Orders}.product_id = ${Products}.id`,
    }
  }
});
```

When users construct metrics, the dimensions and measures they query don’t have to belong to the same cube. They can be located in different places on the data graph. Cube will generate appropriate SQL, including taking care of [chasm / fan-outs][8], to join the underlying tables to respond to the request. 

![open source data modeling - Cube][image03]

**Cube’s approach is different from a query-oriented approach** in which metrics are defined by a custom query and a bunch of attributes are assigned to that query. That approach essentially results in single cubes, each with a single measure and a subset of dimensions and filters. Because these cubes aren’t connected to one another, we can’t reuse dimensions, filters, joins, access controls, caching configuration, etc. 

If we were to continue answering more questions and building more metrics, we would have to build more queries, with duplicated definitions and different join paths. At best, this would be repetitive; at worst, it would lead to inconsistent definitions and a combinatorial explosion of metrics and dimensions. 

Instead, we’re focused on letting users define relationships and reuse definitions to build maintainable and scalable data models.

**Cube’s data modeling language is based on JavaScript.** This may come as a surprise, given that many data modeling frameworks and languages are based on YAML. However, as a markup language, YAML has no runtime and only limited capacity for code organization. This is a big limitation for complex and dynamic data models.

By running a data model in a JavaScript virtual machine, Cube can mutate the model at runtime both reactively and proactively, which enables dynamic data definitions, caching, and access control rules.

**Cube’s data modeling language also manages caching.** Users can define a set of measures and dimensions that are commonly used together, and materialize them as rollup tables. Since the caching rules are defined in code, it’s possible to manage them using best practices including version control, code reviews, and tests in isolated environments.

![open source data modeling - Cube][image06]

**In Cube, data processing and storage are written in Rust**, based on the [Datafusion][9] query execution framework, which uses [Apache Arrow][10] as its in-memory format.

## A data modeling example

Let’s take a look at how to use Cube’s data modeling language.

We’ll model data for an imaginary e-commerce website. Assume the data warehouse has tables like `orders` and `users` with information about transactions and who placed them.

### Orders

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the order</td>
  </tr>
  <tr>
    <td>
      <code>user_id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>value</code>
    </td>
    <td>Value of the order in USD</td>
  </tr>
  <tr>
    <td>
      <code>status</code>
    </td>
    <td>Status of the order, either “processing” or “completed”</td>
  </tr>
</table>

### Users

<table>
  <tr>
    <td>
      <code>id</code>
    </td>
    <td>ID of the user</td>
  </tr>
  <tr>
    <td>
      <code>country</code>
    </td>
    <td>Country of the user</td>
  </tr>
</table>

We can represent these two tables in Cube’s modeling language as cubes with this configuration:


```javascript
cube(`Orders`, {
 sql: `SELECT * FROM orders`,
 measures: {
   totalValue: {
     sql: `value`,
     type: `sum`,
     description: 'Revenue from completed orders placed on our website.',
   }
 },
 dimensions: {
   id: {
     sql: `id`,
     type: `number`,
     primaryKey: true
   },
  
   status: {
     sql: `status`,
     description: 'Status of the order, either processing or completed',
     type: `string`
   },
  
   time: {
     sql: `timestamp`,
     type: `time`
   }
 }
});
```

```javascript
cube(`Users`, {
  sql: `select * from users`,
  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },

    country: {
      sql: `country`,
      type: `string`,
      description: `User's country of residence`
    }
  }
});
```

Let’s look at the major elements of the model.

The `sql` property describes the table of data that will be used in cube, which references the underlying table within the data warehouse. It also can support arbitrary SQL queries in complex cases.

The cubes include defined **_measures_** and **_dimensions_**:

 - ***Measures:*** quantitative data, such as the number of units sold, number of unique visits, revenue, and so forth
 - ***Dimensions:*** properties of data entities, such as state, gender, product name, or units of time
 
To build data relationships, we define the `joins` property on one of the cubes:

```javascript
cube(`Orders`, {
  joins: {
    Users: {
      relationship: `belongsTo`
      sql: `${Orders}.user_id = ${Users}.id`,
    }
  }
});
```

## Querying the data model

With the above data model, it’s now possible to query Cube’s APIs to learn total value grouped by country, or a specific company‘s total value from completed orders.

To find out the total value of orders by country, we’d use the following JSON query:

```json
{
  measures: [Orders.totalValue],
  dimensions: [Users.country]
}
```

You also can explore the data model from within a BI tool:

![querying a Cube data model using Superset][image07]

These examples are relatively simple, but a data model and queries can get very complex, with many measures, dimensions, and relationships.

By creating a data model foundation and exposing metrics via many APIs, Cube ensures that data is accessible and consistent for every downstream application.

 - *Want to explore Cube’s data modeling for yourself? [Create a free Cube Cloud account now][2].*
 - *Have questions or input? [Drop us a line][3], [join our Slack][4], or [view our headless BI project on GitHub][5].*
 - *Want updates like these in your inbox? [Subscribe to our newsletter][6].*

[1]: https://cube.dev/blog/headless-bi
[2]: https://cube.dev/cloud
[3]: https://cube.dev/contact
[4]: https://slack.cube.dev
[5]: https://github.com/cube-js/
[6]: https://cube.dev/#email
[7]: https://www.looker.com/platform/data-modeling/
[8]: https://biguru.wordpress.com/2008/05/01/its-all-in-the-universe-handling-chasm-and-fan-traps/
[9]: https://github.com/apache/arrow-datafusion
[10]: https://arrow.apache.org/
[image01]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/713a8abb-a7d2-4c83-b1f7-6b39bcbbd748.png
[image02]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/26a0660e-b04a-4eaf-b863-7a4c7a72c87f.png
[image03]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/86a88814-86a5-4236-9edf-208d18215ea4.png
[image04]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d4290ab8-3c0d-401b-ae7c-4828b784280f.png
[image05]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d701d0de-1b86-450b-9d2e-b302a5aae7e6.png
[image06]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8b3a6449-c759-4263-9198-87bd29e2b44d.png
[image07]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/688c2958-0016-4d3b-8699-18477d524f8b.png

Artyom Keydunov

Data Modeling

An introduction to open source data modeling in Cube’s headless BI platform.

Cube’s Open Source Data Modeling Language

## SQL Filter Query

In SQL you can set up criteria to query only the specific rows that interest you the most. The WHERE clause is used in SQL filter queries to apply the conditions in SELECT, INSERT, UPDATE, or DELETE sentences.

For the scope of this post, **we’ll describe how to use comparison, special, and Boolean operators with the WHERE clause**. You’ll also learn how to build subqueries and a CASE WHEN expression using WHEN.

We’ll show the examples using the [FIFA players 2018 dataset](https://www.kaggle.com/manjeetsingh/analysis-of-fifa-2018-players-dataset/data) that contains all the statistics and playing attributes of all the players in the full version of FIFA 18.

**Note:** This article covers basic SQL filter queries and is dedicated to SQL beginners. For the sake of simplicity, we took only the first 200 rows from the dataset. The example queries work well with Amazon Redshift, but some of them may conflict with other SQL databases.

## Comparison Operators

In the WHERE clause, you can use the following operators, which are called relational, since they compare the values of two operands:

<table>
<tbody>
<tr><td>=</td><td>Equal. A column or expression matches a value.</td></tr>
<tr><td>&#60;&#62;</td><td>Not equal. Use when you need to exclude a value.</td></tr>
<tr><td>&#62;</td><td>Greater than</td></tr>
<tr><td>&#60;</td><td>Less than</td></tr>
<tr><td>&#62;=</td><td>Greater than or equal</td></tr>
<tr><td>&#60;=</td><td>Less than or equal</td></tr>
</tbody>
</table>

**Note:**

- In some versions of SQL the inequality operator may be written as !=.  
- All uppercase characters are less than all lowercase characters, so ‘Z’ < ’a’, but ‘a’ < ‘z’. Also, all numbers are less than all characters, so ‘1’ < ‘Z’.

```sql
SELECT name, age FROM fifa_dataset WHERE club = ‘Real Madrid CF’;
```

<iframe height="500px" src="https://statsbot.co/i/lg0dgbEMvian4Xft4ju89Six_LrLLV9x0SZUKTtjzUJpLyQmDw1VW70tiWLnyD2_DtXa032BmQrI0my27UBoIQ"></iframe>

_Powered by [Statsbot](https://statsbot.co)_

**Note:** Text fields in SQL require ‘single quotes’, while numeric fields don’t.

## Boolean expressions

[SQL Window Functions Tutorial for Business Analysis](/blog/sql-window-functions-tutorial/)

These expressions test for the truth of some condition. To filter query results in SQL, you can use **AND, OR, and NOT**.

AND. TRUE if all of a set of comparisons are TRUE.

OR. TRUE if either of a set of comparisons is TRUE.

NOT. Reverses the value of any other Boolean operator.

```sql
SELECT name FROM fifa_dataset WHERE club = ‘Real Madrid CF’ AND strength >= 80;
```

<iframe height="300px" src="https://statsbot.co/i/qmTXFjh67hlPU5n3cH-jZ7rjwleIyntIrbBWhxy3jQymP7eS8_P5azvr9um-W4zhpImyR3PxuUI6o1J9XjwaZA"><span data-mce-type="bookmark" css="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start">﻿</span></iframe>

_Powered by [Statsbot](https://statsbot.co)_

Boolean expressions seem to be simple unless you combine them in a complex SQL filter query. You can effectively manage Boolean and relational expressions using parentheses, which determine the order of operations. When you see a long WHERE clause with Boolean expressions, to keep things simple you need to estimate the value of internal operators and then apply the upper external operator.

Keep doing this until the expressions end. ;)

```sql
SELECT name, club FROM fifa_dataset WHERE NOT((age >= 30 AND balance < 70) OR potential < 90);
```

<iframe height="500px" src="https://statsbot.co/i/jduUKzAMyDdDn29IgUKnVJGC1HutCALm-FvBU3MPEQypzDUB0pGLUGBWZaEYWrdGmDQTqktQWfMqwmeoAA9-PQ" iframe=""></iframe>

_Powered by [Statsbot](https://statsbot.co)_

## Special operators

### IN() operator

Instead of writing a complex WHERE clause using Boolean expressions, you can put your values in a list. The IN() operator checks values of a given column by trying to find a match with the values that are inside of a list.

```sql
SELECT name, club FROM fifa_dataset WHERE club in (‘Chelsea’, ‘Liverpool’, ‘FC Barcelona’);
```

<iframe width="70%" height="500px" src="https://statsbot.co/i/VEn4vyGYs-mUNYVruPT2agt1i5jG687Zvj2tG2BhcbwG02z_fpbHHXpxuNimgs5R2739Sy5dR-mHRIV280CV5Q"></iframe>

_Powered by [Statsbot](https://statsbot.co)_

### BETWEEN operator

BETWEEN operator is similar to IN(). Between defines the range of values that should go in decreasing order. A query will return the value in a given range.

```sql
SELECT name FROM fifa_dataset WHERE potential BETWEEN 90 AND 95;
```

<iframe height="500px" src="https://statsbot.co/i/YbmGSYBHO2ka5LR1-eL8C8CbsTFiew7HftbvO6p2dZ8lwJUoTaAWyNr4THBVCd0tNd0LjufkV6Nx6OB2gktTFw"></iframe>

_Powered by [Statsbot](https://statsbot.co)_

**Note:** Unlike from IN, BETWEEN is sensitive to the order, and the first value in the operator must be the first in alphabetical or numerical order.

### LIKE operator

If you are looking for a name or another value and do not remember how exactly they are written, the LIKE (or NOT LIKE) operator is what you need. It looks for values that match a given pattern. As a condition, LIKE uses group symbols (wildcards).

There are two types of wildcards:

- `%` replaces any number of symbols before, inside, or after a search term.
- `_` replaces only one symbol.

```sql
SELECT name FROM fifa_dataset WHERE club LIKE ‘%senal’ and club NOT LIKE ‘Juv_ntus’;
```

<iframe height="500px" src="https://statsbot.co/i/J75S5dkgiPiLWXE0Ts-HrySfdOgrJrRoXKGTR3TbAXjhC-iJu70YwEs3NgK3GeJyiqGr43-8rc46dVNoigl-Gg"></iframe>

_Powered by [Statsbot](https://statsbot.co)_

## Subqueries with the WHEN clause

You can also use aggregate functions in a WHEN clause along with Boolean expressions, but this works only in subqueries.

```sql
SELECT name, age, club FROM fifa_dataset WHERE age =(SELECT min(age) FROM fifa_dataset);
```

<iframe height="200px" src="https://statsbot.co/i/KlFhM5XOAlDeKntxz7l4TAX39M6_IEu3irTDW89CJX-Kkwhp8xea7Gwlhy7TFacYOVVwognmozxTN6zvkgvLMQ"><span data-mce-type="bookmark" css="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start">﻿</span></iframe>

_Powered by [Statsbot](https://statsbot.co)_

**Note:** You don’t need to use subqueries if you use HAVING instead of WHERE for aggregate functions.

## Case When with WHEN clause

[SQL Queries for Funnel Analysis](/blog/sql-queries-for-funnel-analysis)

A more complex SQL filter query (but not always the optimal one) can use a CASE expression in the WHERE clause. The CASE function itself allows you to return a value when it matches your conditions.

```sql
SELECT name, potential, club
FROM fifa_dataset
WHERE
potential = CASE
WHEN potential < 90 THEN potential
END;
```

<iframe height="500px" src="https://statsbot.co/i/I3ZnNbzIoph362HzyoNfzYehLBozkqHEEljJGIvnE4lfhWoSCRN8bHkBTZ__GTGzeQZibOCXbmlGfHO7P5ecug"></iframe>

_Powered by [Statsbot](https://statsbot.co)_

## Conclusion

We reviewed different examples of using the WHERE clause in an SQL filter query. Now you can put the conditions, or so-called predicates, for returning rows. These predicates can be as complex as you need, which gives you infinite possibilities when filtering your data. Learn SQL and watch FIFA 2018. :)

Luba Belokon

How to filter data using the WHERE clause

SQL Filter Query on FIFA Player Data

If you’re new to the big data world and also migrating from tools like Google Analytics or Mixpanel for your web analytics, you probably noticed performance differences. Google Analytics can show you predefined reports in seconds, while the same query for the same data in your data warehouse can take several minutes or even more. Such performance boosts are achieved by selecting random rows or the <a href="https://en.wikipedia.org/wiki/Sampling_(statistics)" target="_blank" rel="noopener">sampling technique</a>. Let’s learn how to select random rows in SQL.

<h1>Sample and Population</h1>

Before we start to work on sampling implementation, it is worth mentioning some sampling fundamentals. Sampling is based on a subset selection of individuals from some population to describe this population’s properties. So if you have some event data, you can select a subset of unique users and their events to calculate metrics that describe all users’ behavior. On the other hand, if you select a subset of events, it won’t describe the behavior of the population precisely.

Given this fact, we assume all data sets to which sampling is applied have unique user identifiers. Moreover, if you have both anonymous user identifiers and authorized user identifiers, we assume using the last one as it delivers more accurate results.

<h1>Selecting Random Rows in SQL</h1>

There’re <a href="https://en.wikipedia.org/wiki/Sampling_(statistics)#Sampling_methods">plenty of different methods</a> you can use to select a subset of users. We’ll consider only two of those as the most obvious and simple to implement: simple random sampling and systematic sampling.

Simple random sampling can be implemented as giving a unique number to each user in a range from 0 to N-1 and then selecting X random numbers from 0 to N-1. N denotes the total number of users here and X is the sample size. Although this method is very simple for understanding, it’s a little bit tricky to implement in an SQL environment, mostly because random number generator outputs don’t scale very well when sample sizes are equal to billions. Also it isn’t very clear as to how to get evenly distributed samples over time.

Bearing this in mind, we’ll use systematic sampling which can overcome these obstacles from an SQL implementation perspective. Simple systematic sampling may be implemented as selecting one user from each M users at a specified interval. In this case, sample size will be equal to N / M. Selecting one user out of M while preserving uniform distribution across sample buckets is the main challenge of this approach. Let’s see how this can be implemented in SQL.

<h1>Sequence Generated User Identifiers</h1>

You’re lucky if your user IDs are integers generated as a strict sequence without gaps like those generated by <code>AUTO_INCREMENT</code> primary key fields. In this case, you can implement systematic sampling as simple as:

```
select * from events where ABS(MOD(user_id, 10)) = 7
```

This SQL query and all SQL queries below are in Standard BigQuery SQL. In this example, we’re selecting one user out of 10, which is a 10% sample. 7 is the random number of the sampling bucket and it can be any number from 0 to 9. We use MOD operation to create sampling buckets which stand for the remainder of a division by 10 in this particular case. It’s really simple to show that if <code>user_id</code> is a strict integer sequence, then user counts are uniformly distributed across all sampling buckets when user count is high enough.

To estimate the event count, for example, you can write something like:

```
select count(*) * 10 as events_count from events where ABS(MOD(user_id, 10)) = 7
```

Please note the multiplication by 10 in this query. Given the fact that we use a 10% sample, all estimated additive measures should be scaled by 1/10% in order to match real values.

One can question how precise this sampling approach is for a specific dataset. You can estimate it by checking how uniform the distribution is within sampling buckets. To do that you can query something like:

```
select ABS(MOD(user_id, 10)), count(distinct user_id) from events GROUP BY 1
```

Let’s consider some example results of this query:

<table class="ui table">
<tbody>
<tr>
<td><span css="font-weight: 400;">0</span></td>
<td><span css="font-weight: 400;">115617904</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">1</span></td>
<td><span css="font-weight: 400;">115639491</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">2</span></td>
<td><span css="font-weight: 400;">115635158</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">3</span></td>
<td><span css="font-weight: 400;">115631186</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">4</span></td>
<td><span css="font-weight: 400;">115614455</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">5</span></td>
<td><span css="font-weight: 400;">115636846</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">6</span></td>
<td><span css="font-weight: 400;">115644374</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">7</span></td>
<td><span css="font-weight: 400;">115652641</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">8</span></td>
<td><span css="font-weight: 400;">115648788</span></td>
</tr>
<tr>
<td><span css="font-weight: 400;">9</span></td>
<td><span css="font-weight: 400;">115654729</span></td>
</tr>
</tbody>
</table>

We can calculate the mean and confidence interval for an alpha coefficient equal to 0.01 for these sampling bucket sizes. The <a href="https://en.wikipedia.org/wiki/Confidence_interval#Basic_steps">confidence interval</a> will be equal to 0.01% of the sampling bucket average size. This means that with 99% probability the sampling bucket sizes differ not more than 0.01%. Different metrics calculated with these sampling buckets couple with this statistic but don’t inherit it. So, in order to calculate precision for event count estimation, you can calculate the event count for each sample as:

```
select ABS(MOD(user_id, 10)), count(*) * 10 from events GROUP BY 1
```

Then calculate absolute and relative confidence intervals for these event counts as in the case of user count to get event count estimate precision.

<h1>String Identifiers and Other User Identifiers</h1>

<p>In the case that you have string user identifiers or integers, but not a strict sequence instead of integer sequence identifiers, you need a way to distribute all user IDs between different sampling buckets in a uniform manner. <a href="https://en.wikipedia.org/wiki/Hash_function#Uniformity">This</a> can be done by hash functions. Not all hash functions can get you uniform distribution under different circumstances. You can check <a href="https://github.com/rurban/smhasher/tree/master/doc">smhasher</a> test suite results to check how good a particular hash function is at this.</p>

For example, in BigQuery you can use the <code>FARM_FINGERPRINT</code> hash function to prepare a sample for select:

```
select * from events where ABS(MOD(FARM_FINGERPRINT(string_user_id), 10)) = 7
```

<code>FARM_FINGERPRINT</code> can be replaced with any suitable hash function, such as <code>xxhash64</code> in Presto or even the combination of <code>md5</code> and <code>strtol</code> in Redshift.

As in the case of sequence-generated user identifiers you can check uniformity statistics as:

```
select ABS(MOD(FARM_FINGERPRINT(string_user_id), 10)), count(distinct string_user_id) from events GROUP BY 1
```

<h1>Common Pitfalls</h1>

Most often, sampling can reduce your SQL query execution time by more than 5-10 times without being harmful in terms of precision, but there are still cases where you should be cautious. This mostly belongs to a sampling bias problem because of sample size. It can occur when dispersion of metrics you’re interested in between samples is too high even if the sample size is big enough.

For example, you can be interested in some rare event count such as an enterprise demo request being some B2C site with a huge amount of traffic. If the enterprise demo request count is about 100 events per month and your monthly active users is about 1M, then sampling can lead you in a situation where the enterprise demo request count estimate with a 10% sample produces significant errors. Generally speaking, sampling random rows in SQL should be avoided in this case or <a href="https://en.wikipedia.org/wiki/Sampling_(statistics)#Stratified_sampling">more sophisticated methods</a> should be used instead.

As always don't hesitate to leave your comments or <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=random_rows">contact our team</a> to get help.

Pavel Tiunov

Big Data sampling in SQL

Scalable Select of Random Rows in SQL

Many of “out-of-the-box” analytics solutions come with automatically defined user sessions. It’s good to start with, but as your company grows, you’ll want to have your own session definitions based on your event data. Analyzing user sessions with SQL gives you flexibility and full control over how metrics are defined for your unique business.  

## What is a session and why should I care?
The session is usually defined as **a group of interactions that one user executes within a given time frame on your app.** Usually, that time frame defaults to 30 minutes. One session includes any events which a user completes on your app before leaving, for example, visiting pages, downloading materials, performing actions.

![](https://media.graphcms.com/chXMIfwwTICos0Hlh06y)

Having sessions in place, we’ll be able to answer questions like:

- How much time did users spend on the app?
- What is the bounce rate?
- What are the most used areas of the app?
- Where are the users who make target actions coming from?
- Where are users spending most of their time?

### Defining user sessions right
To define a user session we need to have an event table in our database with at **least user_id** and **timestamp.**

![](https://media.graphcms.com/Fccj465SV6XdgPl7MukA)

Usually, you will have a lot of additional information in the event tables, such as event type, name, device info, referrer, and much more. All these properties are going to be very useful to give more context to our sessions and to build things, such as an attribution model.

_**Note: In this post, we’re going to use [window functions](https://statsbot.co/blog/sql-window-functions-tutorial), so the following example will not work with MySQL. Also, all these queries are dedicated to Redshift database.**_

### Step 1
The first step for defining user sessions with SQL is to find out the difference between the timestamp of the current row and the previous one, by _**user_id**_. We will use [LAG function](https://www.postgresql.org/docs/current/static/functions-window.html) to accomplish it. This will give us an inactivity time between events.

```
SELECT
	*
	, DATEDIFF(minutes, lag(timestamp) over (PARTITION BY user_id order by timestamp), timestamp) as inactivity_time
FROM events
```

The _**inactivity_time**_ for the first event is NULL, since it’s the first event and we don’t have anything before it.

### Step 2
We can use _**inactivity_time**_ to group events into sessions based on 30 minute intervals of inactivity. First, we’ll select all events where _**inactivity_time**_ is either NULL, or more than 30 minutes, which means it is the first event in the session.

Based on this first event, we define _**session_start_at**_,  which is the timestamp of the first event. We use [ROW_NUMBER function](https://www.postgresql.org/docs/current/static/functions-window.html) to calculate session sequence, which is used in _**session_id**_.

```
SELECT
      event.user_id || '-' || row_number() over(partition by event.user_id order by event.timestamp) as session_id
      , event.user_id
      , event.timestamp as session_start_at
      , lead(timestamp) over(partition by event.user_id order by event.timestamp) as next_session_start_at
    FROM
      (SELECT
            e.user_id
	     , e.timestamp
             , DATEDIFF(minutes, LAG(e.timestamp) OVER(PARTITION BY e.user_id ORDER BY e.timestamp), e.timestamp) AS inactivity_time  
          FROM events AS e
      ) as event
    WHERE (event.inactivity_time > 30 OR event.inactivity_time is null)
```

We can save this table as **sessions** Data Mart to use it in our future queries.

Once we have this table, it’s easy to answer user analytics questions we outlined in the beginning. For example, to calculate average session duration we can use the following SQL.

```
SELECT
  COUNT(*) AS sessions_count,
  AVG(duration) AS average_session_duration
FROM (
  SELECT session_id
        , DATEDIFF(minutes, MIN(events.timestamp), MAX(events.timestamp)) AS duration
  FROM sessions
  LEFT JOIN events on events.user_id = sessions.user_id
        AND events.timestamp >= events.session_start_at
        AND (events.timestamp < sessions.next_session_start_at OR sessions.next_session_start_at is null)
  GROUP BY 1
)
```

As you see, we join _**events table**_ to _**sessions**_ to map every event to its session. It allows us to get the end of each session, which is the max timestamp of the event within the given session. More complex calculations of session duration could optionally include a window of inactivity as well.

What to learn more about Statsbot? [Try it now.](https://statsbot.co/form-trial)

Analyzing user sessions with SQL

Event Analytics: How to Define User Sessions with SQL

<em>The <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=ltv&amp;utm_content=predict_ltv">Statsbot</a> team estimated LTV 592 times for different clients and business models. We share our experience in this post and in a <strong><a href="https://statsbot.co/ltv-ebook?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=ltv&amp;utm_content=predict_ltv">free ebook</a></strong> on how to calculate customer lifetime value with SQL without sophisticated statistical models.</em>

<button url="https://statsbot.co/ltv-ebook?utm_source=statsbotblog&utm_medium=article&utm_campaign=ltv">Get a Free Ebook on Estimating LTV</button>

<span css="font-weight: 400;">Customer lifetime value, or LTV, is the amount of money that a customer will spend with your business in their “lifetime,” or at least, in the portion of it that they spend in a relationship with you. It’s an important indicator of how much you can spend on acquiring new customers. <strong>For example, your customer acquisition cost (CAC) is $150, and LTV is $600.</strong> You would be able to increase the budget to get more people and grow your business. The balance between CAC and LTV allows you to check any business for market survival.</span>

<span css="font-weight: 400;">Estimating LTV is a predictive metric which depends on future purchases, based on past patterns, and allows you to see how much risk you are exposed to as a business, and how much you can afford to spend to acquire new clients. At an individual level, it also enables you to figure out who your highest-value customers are likely to be, not just now, but also in the future. </span>

<span css="font-weight: 400;">In order to understand how to estimate LTV, it is useful to first think about evaluating a customer’s lifetime value at the end of their relationship with us. So, say a customer stays with us for 12 months, and spends $50 per month.</span>

<figure><img src="https://media.graphcms.com/1pHvnSjGSZWL7w2geaKt" /></figure>

<span css="font-weight: 400;">The revenue that they generated for our business over their lifetime is then $50*12 = $600.  Simple! We can consider the </span><i><span css="font-weight: 400;">basic definition of LTV</span></i><span css="font-weight: 400;"> as a sum of payments from a specific user.</span>

<span css="font-weight: 400;">This same principle applies to the group. If we want to see</span><i><span css="font-weight: 400;"> the average LTV </span></i><span css="font-weight: 400;">for the group we can look at total spend divided by the number of customers. When we’re talking about </span><i><span css="font-weight: 400;">estimating LTV </span></i><span css="font-weight: 400;">for the group</span><i><span css="font-weight: 400;">, or predictive LTV, </span></i><span css="font-weight: 400;">we need to take into account how long customers stay with us. To get that, we actually look at it “backwards”: we look at how many customers we lose over time, or the </span><i><span css="font-weight: 400;">churn rate</span></i><span css="font-weight: 400;">.</span>

<aside><h2><strong><a href="https://blog.statsbot.co/how-to-reduce-churn-rate-by-27-by-handling-stripe-failed-payments-e75892f8956c">How to Reduce Churn Rate By Handling Stripe Failed Payments</a></strong></h2></aside>

<h1><span css="font-weight: 400;">How do you calculate LTV for SaaS?</span></h1>

<span css="font-weight: 400;">At a group level, the basic formula for estimating LTV is this:</span>

<figure><img src="https://media.graphcms.com/Y7XAnM7R4Wjj1QCObxFK" /></figure>

<span css="font-weight: 400;">Where ARPU is average monthly recurring revenue per user and the churn rate is the rate at which we are losing customers (so the inverse of retention). </span>

<span css="font-weight: 400;">This basic formula can be obtained from assumption:</span>

<b><i>Next Month Revenue = (Current Month Revenue) * (1 - Churn Rate)</i></b>

<i><span css="font-weight: 400;">Note: When we’re estimating customer lifetime value for SaaS we can neglect Gross Margin, because costs are minor and don’t affect the accuracy of a result. But when we calculate predictive LTV for ecommerce later in this article, we’ll include COGS in our formula.</span></i>

<span css="font-weight: 400;">The main limitation of the LTV formula above is that it assumes that churn is linear over time, as in: we are as likely to lose a customer between the first month of membership of our service and the second, as we are to lose them much later on. Going deeper into the nature of predictive LTV, we can say that it’s a sum of a </span><a href="https://en.wikipedia.org/wiki/Geometric_series"><span css="font-weight: 400;">geometric series</span></a><span css="font-weight: 400;">, and linear churn doesn’t look like a straight line (as is shown in many articles about LTV). </span>

> <span css="font-weight: 400;">In fact, we know that linear churn is usually not the case. </span>

<span css="font-weight: 400;">In a flexible subscription model, we lose many people at the very beginning, when they are “testing out” a service, but once they have been with us for a long time, they are less likely to leave. </span>

<span css="font-weight: 400;">Ultimately, it depends on the type of contract that exists between customers and the business: for example, annual renewals, where churn is more linear, will result in LTV that is very close to the formula above.</span>

<span css="font-weight: 400;">Services which do not have any contracts may lose a high percentage of their new customers, but then churn may slow down.</span>

<span css="font-weight: 400;">We can think of this concept graphically: </span>

<figure><img src="https://media.graphcms.com/eHFY3aPTUWfUvzmMIBvA" /></figure>

<span css="font-weight: 400;">If the LTV of the group is the area under the line, we can very clearly see that the rate at which we lose customers will impact our LTV estimates very significantly. So we will need to take this into account when we are making our calculations. For a first estimate of LTV, however, it makes sense to go with the simplest formula. After that, we will add levels of complexity.</span>

<h1><span css="font-weight: 400;">Extracting ARPU and churn using SQL</span></h1>

<aside><h2><a href="https://statsbot.co/ltv-ebook?utm_source=blog&amp;utm_medium=article&amp;utm_campaign=ltv&amp;utm_content=predict_ltv">FREE Ebook on Calculating LTV with SQL</a></h2></aside>

<span css="font-weight: 400;">In order to make the most basic estimate of LTV, we need to look at our transaction history. Then, we can establish the average revenue per customer as well as the churn rate over the period that we are looking at. For simplicity, I’m going to look at the last year.</span>

<span css="font-weight: 400;">You can calculate ARPU in 2 steps:</span>

```
month_ARPU AS
(SELECT
     visit_month, 
     Avg(revenue) AS ARPU 
FROM
     (SELECT
          Cust_id,
          Datediff(MONTH, ‘2010-01-01’, transaction_date) AS visit_month, 
          Sum(transaction_size) AS revenue 
     FROM   transactions 
     WHERE  transaction_date > Dateadd(‘year’, -1, CURRENT_DATE) 
     GROUP BY
           1, 
           2) 
GROUP BY 1)
```

<span css="font-weight: 400;">The results will look like this:</span>

<figure><img src="https://media.graphcms.com/BxnzN8snROGRB1jt5bVZ" /></figure>

<span css="font-weight: 400;">In the case above, that would give us an average monthly spend of </span><b>$987.33.</b>

<span css="font-weight: 400;">Calculating churn rate is a bit more complicated, as we need the percentage of people </span><b>not </b><span css="font-weight: 400;">returning from one month to the next, taking each group of customers according to the month of their first visit, and then checking if they came back or not in the following month.</span>

> <span css="font-weight: 400;">The problem is always that, in a transactional database, we have customers’ visits on separate lines, rather than all on the same line. </span>

<span css="font-weight: 400;">The way to fix that problem is to join the transactional database to itself, so that we can see a customer’s behavior on one single line.</span>

<span css="font-weight: 400;">In order to isolate those who churned, we take the visits from month 1, and left join the visits from month 2 on the cust_id. The lines where visits from month 2 have a cust_id that is null are the ones where the customer has not returned.</span>

```
WITH monthly_visits AS 
(SELECT
     DISTINCT
     Datediff(month, ‘2010-01-01’, transaction_date) AS visit_month, 
     cust_id 
FROM            transactions 
WHERE
transaction_date > dateadd(‘year’, -1, current_date)), 

(SELECT
avg(churn_rate) 
FROM
     (SELECT
          current_month, 
          Count(CASE 
               WHEN cust_type='churn' THEN 1 
               ELSE NULL 
     	  END)/count(cust_id) AS churn_rate 
     FROM
          (SELECT
               past_month.visit_month + interval ‘1 month’ AS current_month, 
               past_month.cust_id, 
               CASE
                    WHEN this_month.cust_id IS NULL THEN 'churn' 
                    ELSE 'retained' 
               END AS cust_type 
          FROM
               monthly_visits past_month 
  	       LEFT JOIN monthly_visits this_month ON
                    this_month.cust_id=past_month.cust_id
                    AND this_month.visit_month=past_month.visit_month + interval ‘1 month’
          )data
     GROUP BY 1)
)
```

<span css="font-weight: 400;">Say this gives us a result of 0.1, just for simplicity.</span>

<figure><img src="https://media.graphcms.com/gAWIhxiSLWLLjLBqDHmA" /></figure>

<span css="font-weight: 400;">It is a simple calculation, then, to estimate LTV: we have monthly ARPU and monthly churn, so we just divide one by the other!</span>

<span css="font-weight: 400;">$987.33/0.1 = </span><b>$9873.3</b>

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/customer-retention-analysis/">A Guide for Customer Retention Analysis with SQL</a></h2></aside>

<span css="font-weight: 400;">As stated earlier, there are limits to this formula, mostly because it makes a series of assumptions that may not hold in the real world. The main one is that retention and churn rates are </span><i><span css="font-weight: 400;">stable both across cohorts and across time</span></i><span css="font-weight: 400;">.</span>

<span css="font-weight: 400;">Stability across cohorts implies that early adopters of your service act in similar ways to late adopters, while stability across time implies that customers’ likelihood of churning out is the same at the beginning of their relationship with you as it is, for example, 2 years in. Depending on how close to the truth these assumptions are, you may need to revise your LTV estimate downwards.</span>

<strong>If you want to lean how to estimate LTV for ecommerce, for cohorts and each individual customer, download our FREE ebook on calculating customer lifetime value with SQL. </strong>

<button url="https://statsbot.co/ltv-ebook?utm_source=statsbotblog&utm_medium=article&utm_campaign=ltv">Get a Free Ebook on Estimating LTV</button>

Estimating LTV for SaaS and ecommerce using SQL

Calculating Customer Lifetime Value: SQL Example

Alex Yeskov

The most popular business problems solved with SQL

SQL Window Functions Tutorial for Business Analysis

Analyzing funnels in an SQL database is not a hard task when you know the right queries. Using SQL language, you can dive into complex funnels and event flow analysis to gain insights into your users’ behavior. In this article, we’re going to tell how we, at <a href="https://statsbot.co/cloud-bi?utm_source=newblog&amp;utm_medium=article&amp;utm_campaign=sql_funnel" target="_blank" rel="noopener">Statsbot</a>, use SQL queries for conversion funnel analysis.

<h3><strong>Why to build funnels</strong></h3>

Funnels are an extremely useful and helpful data tool that can provide you a great overview about your product. In a nutshell, funnels are representing several events your users perform one after another. Calculating how many unique users made each event could show you a <strong>conversion rate between each step</strong>, so you could actually localize a problem down to a certain stage.

At Statsbot, we measure how many people signed up, connected integration, and used the <em>Scheduled Report</em> feature. Users who performed all these actions are considered as successfully on-board.

Let’s take this process as an example and see how we can actually create this funnel with SQL. The following queries are written for PostgreSQL. You can just copy them and build your funnel right away.

<h3><strong>Step 1 — Signup event</strong></h3>

In our DB we have a table called <em>Bots</em>. Any user who signed up to Statsbot has a unique ID number and they get added to this table, so bots = users in our case.

In order to get the number of signups, we simply need to choose <em>Bots</em> table, apply the date range we need, and specify the function, which is count.

```
SELECT count(*)
FROM bots
WHERE created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’
```

We can see now that during this time we got 745 signups.

<figure><img src="https://media.graphcms.com/tbQYlSx0RO2zGOfdwdfa" /></figure>

<h3><strong>Step 2 — Calculating Integration connected event</strong></h3>

Next, we need to find out how many out of those 745 users connected at least one integration.

In our DB we have a table called “Integrations” that contains an integer value representing how many integrations have been connected by user.

We now need to make a following SQL query to get the result. Note that we are querying a number of integrations connected by users out of those who signed up in May, which we defined in Step 1.

```
SELECT count(*)
FROM bots
WHERE 
  (SELECT count(*)
  FROM integrations
  WHERE integrations.bot_id = bots.id)>0
  AND created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’
```

Awesome! The result is 104 integrations that had been connected by users who signed up in May.

<h3><strong>Step 3 — How many users scheduled at least one report?</strong></h3>

Okay, the next step is exactly the same as the previous one. We just add another event from a <em>scheduled_reports</em> section with the same condition to complete this nested query.

```
SELECT count(*)
FROM bots
WHERE 
   (SELECT count(*)
   FROM integrations
   WHERE integrations.bot_id = bots.id)>0
AND
   (SELECT count(*)
   FROM scheduled_reports
   WHERE scheduled_reports.bot_id = bots.id)>0
AND created_at BETWEEN ‘2017-05-01’ AND ‘2017-05-31’
```

The result is 61 users have scheduled at least one report.

<figure><img src="https://media.graphcms.com/gSYs76YFTNOoVP3rYLNE" /></figure>

<h3><strong>Step 4 — Putting all pieces together for creating a funnel analysis</strong></h3>

This is where the most interesting part comes in. Let’s take all the logic we’ve worked on together and create the conversion funnel — our initial goal.

```
SELECT count(*) AS Step1,
count(*) FILTER(
  WHERE (
   SELECT count(*)
   FROM integrations
   WHERE integrations.bot_id = bots.id
 ) > 0
) AS Step2,
count(*) FILTER(
  WHERE (
   SELECT count(*)
   FROM integrations
   WHERE integrations.bot_id = bots.id
 ) > 0 and (
   SELECT count(*)
   FROM scheduled_reports
   WHERE scheduled_reports.id = bots.id
 ) > 0
) AS Step3
FROM bots
WHERE created_at BETWEEN '2017-05-01' AND '2017-05-31'
```

This is what our final result looks like after visualization.

<figure><img src="https://media.graphcms.com/fc9YnbmOQ4GO6pdeRfvo" /></figure>

This funnel gives us the insight that we have some problems with onboarding and connecting the first integration. Then, we can make some changes and build the same funnel in one week to measure whether the conversion rate increases or decreases.

Further, we can detect 43 users who connected the data source but didn’t schedule the report yet. They are <a href="http://statsbot.co/blog/how-to-reduce-churn-rate-by-handling-stripe-failed-payments/" target="_blank" rel="noopener">in churn risk</a>, and we need to get in touch with them quickly to get back on track.

<strong>You should also remember one important thing: we created a funnel, which is affected by a</strong><a href="https://en.wikipedia.org/wiki/Slowly_changing_dimension" target="_blank" rel="noopener"><strong> slowly changing dimension</strong></a><strong> problem.</strong> In our case, users are signed up in May, but at the same time they could add more integrations and schedule more reports during their customer life cycle. This might result in changes to the numbers in Step 2 and Step 3.

If you have an event based system, then a slowly changing dimension problem can be eliminated. For example, we use Mixpanel to build the same funnel this way:

```
SELECT
  COUNT(
  DISTINCT CASE WHEN cur_signup_time IS NOT NULL
    THEN distinct_id END
  ) Step1,
  COUNT(
  DISTINCT CASE WHEN cur_signup_time IS NOT NULL AND cur_int_time IS NOT NULL
    THEN distinct_id END
  ) Step2,
  COUNT(
  DISTINCT CASE WHEN
    cur_signup_time IS NOT NULL AND cur_int_time IS NOT NULL AND cur_new_schedule_time IS NOT NULL
    THEN distinct_id END
  ) Step3
FROM (
       SELECT
         distinct_id,
         "time",
         COALESCE(signup_time,
                  LAG(signup_time) IGNORE NULLS OVER(PARTITION BY distinct_id
                  ORDER BY time)
         ) cur_signup_time,
         COALESCE(int_time,
                  LAG(int_time) IGNORE NULLS OVER(PARTITION BY distinct_id
                  ORDER BY time)
         ) cur_int_time,
         COALESCE(new_schedule_time,
                  LAG(new_schedule_time) IGNORE NULLS OVER(PARTITION BY distinct_id
                  ORDER BY time)
         ) cur_new_schedule_time
       FROM
         (
           SELECT
             "time",
             distinct_id,
             (CASE WHEN event = 'Signup'
               THEN time END) signup_time,
             (CASE WHEN event = 'Integration connected'
               THEN time END) int_time,
             (CASE WHEN event = 'New Schedule'
               THEN time END) new_schedule_time
           FROM mixpanel.mixpanel_export
           WHERE "time" > '2016-05-01' AND "time" < '2017-01-01'
           ORDER BY distinct_id, "time"
         ) event_times
       ORDER BY distinct_id, "time"
     ) event_windows
```

These 4 steps are easy to follow to build your first funnel in SQL. We hope this will be a good start to customizing your analysis with SQL power!