forked from sqlpage/SQLPage
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path54_blog_bompard.sql
141 lines (86 loc) · 9.44 KB
/
54_blog_bompard.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
INSERT INTO blog_posts (title, description, icon, created_at, content)
VALUES
(
'How I built a 360° performance monitoring tool with SQL Queries',
'Alexis built a performance monitoring tool with SQLPage for a 100M€/year company',
'shirt',
'2025-01-25',
'
# How I Built And Deployed An Exhaustive Performance Monitoring Tool For a 100M€/year Company Using SQL Queries Only
### What is SQLPage ?
> [SQLPage](http://sql-page.com) allows anyone with SQL skills to build and deploy digital tools (websites, data applications, dashboards, user forms…) using only **SQL queries**. Official website: [https://sql-page.com/](https://sql-page.com/)
SQLPage eliminates the need to learn server languages, HTML, CSS, JavaScript, or front-end frameworks, and instead uses SQL to generate modern UI layouts populated with database query results. You get native SQL interactions with the database, without all the other layers that typically get in the way.
The execution of the project is straightforward: simply run a single executable without any installation dependencies. Everything from authentication to security, and even HTTPS termination is automated. The code required to complete most real-world development tasks is minimal and seamless.
Finally, it’s open source with an MIT license.
### Why SQLPage became a game-changer for me, as a Head of Data
As a Head of Data at a mid-size company, I understand the challenge of juggling multiple tools — often expensive and proprietary — alongside a variety of dashboards. Building an **all-in-one**, **user-friendly**, **mobile-compatible** platform for data monitoring and visualization that serves everyone, from C-level executives to store managers, is no small feat.
The struggle intensifies when teams are small and lack coding skills or experience with diverse tech stacks. A typical data flow in a digital-native company involves several teams, specialized skills, and costly tools:
*Typical Data Flow of digital native companies.
SQLPage changes this by allowing data professionals to use the same language — SQL — across the entire process, from building data pipelines to creating fully functional digital tools. Data analysts, scientists, business analysts, DBAs, and IT teams already have the expertise to craft their own custom data applications from the ground up.
### Building an all-in one monitoring tool using SQL-queries only
[](https://www.youtube.com/embed/R-5Pej8Sw18?si=qgxacwip2Mm-0wC7)
*Excerpt from a series of videos explaining how to build and deploy your first digital tool with SQLPage* ([https://www.youtube.com/@SQLPage](https://www.youtube.com/@SQLPage)).
I am using SQLPage to build a 360° Performance tool for my company, integrating data from multiple sources — Revenue, traffic, marketing investments, live performance monitoring, financial targets, images of top sold products, Google Analytics for the online traffic… — .
#### With SQLPage, I can:
- **Centralize** all company data in one tool for visualizations, year-over-year comparisons, financial targets, and more.
- **Provide tailored insights**: A store owner can instantly access last year’s performance and top-selling products, while the e-commerce director can track conversion rate history. SQLPage’s pre-built components offer limitless possibilities for displaying results.
- **Perform CRUD operations**: Unlike traditional BI tools, SQLPage not only displays data but also allows users to interact with it — inputting data, such as comments or updates, directly through the interface. This capability to both display and collect data is a significant enhancement over traditional BI tools, which typically do not support data input.
- **Ensure a single source of truth**: By connecting directly to the database, SQLPage avoids discrepancies between dashboards, ensuring all teams work with consistent and accurate data.
Here are some pages I built using only SQL queries, allowing everyone in the company to instantly access any level of information, from the fiscal year 2024 revenue trends to the top-selling products in Marseille in October 2022.
*Performance of different channels vs last year and best sellers.
*Examples of views built with SQLPage to provide a 360° tool for the company.
### How Does It Work ?
The process in SQLPage follows a simple pattern:
> 1) Select a component
> 2) Write a query to populate the selected component with data
You can find the full list of components: [https://sql-page.com/documentation.sql](https://sql-page.com/documentation.sql)
Here’s an example of a parameterized SQL query that uses the “chart” component, along with the query to feed data into it:
```sql
-- Chart Component
SELECT ''chart'' AS component,
CONCAT(''Daily Revenue from '', $start_date_comparison, '' to '', $end_date_comparison) AS title,
''area'' AS type,
''indigo'' AS color,
5 AS marker,
0 AS ymin;
-- Chart Data Query
SELECT DATE(business_date) AS x,
ROUND(SUM(value), 2) AS y
FROM data_example
WHERE DATE(business_date) BETWEEN $start_date_comparison AND $end_date_comparison
AND variable_name = ''CA''
GROUP BY DATE(business_date)
ORDER BY x ASC;
-- NB: The variables $start_date_comparison and $end_date_comparison are
-- defined dynamically in the SQL script
```
And the result:
*Example of a SQL-generated page using the components “graphs” and “tables”.*
That’s it! Each component comes with customizable parameters, allowing you to tailor the display. As shown in the screenshot, links are clickable, enabling users to add data, such as leaving a comment for a specific date.
The ability to perform CRUD operations and interact directly with databases is a game changer compared to traditional BI tools. You can try it yourself by clicking “add” in the column “COMMENT THIS YEAR” [https://demo-test.datapage.app/lets_see_some_graphs.sql](https://demo-test.datapage.app/lets_see_some_graphs.sql)
### What About GenAI ?
I couldn’t write an article about data in 2024 without mentioning GenAI. The great news is that SQLPage, relying solely on SQL queries, is naturally GenAI-friendly. In fact, I rarely write SQL queries myself anymore — I let GenAI handle that. My workflow in SQLPage now becomes:
> 1) Select a component
> 2) Ask a GenAI tool to write the query I need
*Example of Generated SQL to display a specific format of numbers.*
### How to Host Your SQLPage Application
Once my app was ready, I could have chosen to host it myself on any server for a few euros a month, but I opted for SQLPage’s official hosting service, DataPage ([https://datapage.app/](https://beta.datapage.app/)), which is fully managed and very convenient. My app was hosted at _domainname.datapage.app._ The service includes a Postgres database, allowing you to either store your data on the server or connect directly to your existing database (Microsoft SQL Server, SQLite, Postgres, MySQL, etc).
### What Difficulties Can Be Encountered With SQLPage
While SQLPage simplifies the process of building digital tools, it does come with some challenges.
As applications grow in complexity, so do the SQL queries required to power them, which can result in long and intricate scripts. Additionally, to fully leverage SQLPage, you need to understand how its components work, especially if user input is involved. Developers should be comfortable with creating tables in a database, writing `INSERT` queries, and managing data effectively. Without a solid grasp of these fundamentals, building more advanced apps can become a bit overwhelming.
### Conclusion
With SQLPage, any company with a database and one employee who knows how to query it has the tools and workforce to build and deploy virtually any digital tool.
In this article, I focused on creating an enhanced Business Intelligence tool, but SQLPage’s versatility goes far beyond that. It is being used to build a planning tool for lumberjacks in Finland, a monitoring app for a South African transport and logistics company, by archaeologists to input excavation data in the field…
What all these projects have in common is that they were built by a single person, using nothing but SQL queries. If you’re ready to streamline your processes and build powerful tools with ease, SQLPage is worth exploring further.
### Useful links
- 🏡Official website [https://sql-page.com](https://sql-page.com/)
- 🔰Quick start (written by [Nick Antonaccio](https://medium.com/u/b6a791990395)): [https://learnsqlpage.com/sqlpage_quickstart.html](https://learnsqlpage.com/sqlpage_quickstart.html)
- 📹Youtube tutorial videos on SQLPage channel: [https://www.youtube.com/@SQLPage/playlists](https://www.youtube.com/@SQLPage/playlists)
- 🤓github: [https://github.com/sqlpage/SQLPage/](https://github.com/sqlpage/SQLPage/)
- ☁️Host your applications: [https://datapage.app](https://datapage.app)
');