Intro to Analytical Functions

Let’s start with a simple real-life example. Imagine you own a coffee shop. Every day, customers come in, place orders, and you log this data. After a while, you want to know: what drinks are the most popular? What was the average bill over the last three months? Which days of the week bring in the most revenue?

These questions are examples of analytics tasks. Analytics in this case helps turn a “pile of data” into useful info. And working with analytics inside databases isn’t magic—it’s just a set of tools that let you answer questions like these.

Analytics often solves tasks like:

• Analyzing key metrics (like revenue, average bill, number of orders).
• Building reports: tables, charts, diagrams.
• Monitoring trends: how metrics change over time.
• Comparing data (for example, from one region to another).

This is super important for making solid business decisions, optimizing processes, or even forecasting future results.

The Role of PL/pgSQL in Analytics

Now that we get why analytics matters, here’s the question: why use PL/pgSQL for this? I mean, you could just export the data to Excel and crunch the numbers there, right?

PL/pgSQL lets you:

• Automate calculations. Why repeat stuff by hand if you can automate it?
• Handle big data volumes on one side. Generating reports on the server means you don’t have to send tons of data to client apps.
• Create powerful queries with minimal load. Work fast and efficiently with big tables.

Example: you can automatically generate a daily sales report and save it to a table, so you don’t have to do it manually.

Benefits of Using Procedures to Automate Analytics

The manual analytics process:

1. Grab data from the database.
2. Process it in some spreadsheet.
3. Save the results. Automation with PL/pgSQL:
4. Write the procedure once.
5. Run it on a schedule.
6. Enjoy the results.

Totally different level of practicality, right? :)

Examples of Tasks Solved by Analytical Functions

Here are a few tasks you can solve with analytical functions. These examples will help you see that analytical functions aren’t some abstract thing—they’re a super useful tool.

Calculating Key Metrics

Key metrics are business indicators that help you gauge how things are going. For example:

• Average bill: how much a customer spends on average per purchase.
• Total number of orders for a given period.
• Sales revenue by product category.

Example SQL query for average bill:

SELECT 
    SUM(order_amount) / COUNT(*) AS average_order
FROM 
    orders
WHERE 
    order_date > CURRENT_DATE - INTERVAL '3 months';

Building Time Series and Trends

Say you want to see how sales change day by day. For this, you can aggregate data by date and build time series.

SELECT
    order_date, 
    SUM(order_amount) AS daily_sales
FROM 
    orders
GROUP BY 
    order_date
ORDER BY 
    order_date;

Comparing Data by Periods

Now imagine you want to compare revenue for October and September:

SELECT 
    EXTRACT(MONTH FROM order_date) AS month, 
    SUM(order_amount) AS total_sales
FROM 
    orders
WHERE 
    order_date BETWEEN '2023-09-01' AND '2023-10-31'
GROUP BY 
    EXTRACT(MONTH FROM order_date);

This approach lets you draw conclusions: what changed between the months? Why was September more successful? Or maybe the other way around?

How Does This Work in Real Life?

In real practice, analytics is used in scenarios like:

1. In marketing: to figure out which channels bring in more customers.
2. In finance: for calculating revenue, losses, and forecasting.
3. In software development: for analyzing app performance.
4. In the gaming industry: for analyzing user behavior and building personalized offers.

Now that you know why analytical functions matter, in the next lectures we’ll dive into the technical stuff, like using window functions and aggregates to build reports. For now—try to imagine what metrics you’d want to analyze, and how that could help your project!

2

Task

SQL SELF, level 59, lesson 0

Locked

Monthly Revenue

Monthly Revenue

Open