Category: SQL
-
Navigating SQL Hierarchies: Finding the Ultimate Parent
Untangling the web of parent-child relationships across multiple hierarchical levels can be challenging, yet it’s crucial for insightful data analysis. Frequently, we need to identify the apex of these hierarchies, the ‘ultimate parent’, in order to group data for analysis. However, the unpredictable number of levels within these hierarchies can complicate this task. In this…
-
A Short Practical Guide to Window Functions in SQL
A window function allows you to concisely compare rows in a single table.
-
Speed up Long-Running SQL Queries Using a For Loop in Python
If you have a long-running query, splitting it up into smaller queries can help with performance. With Python, we can dynamically loop through each query.
-
Leverage Subqueries in SQL for Dynamic and Efficient Queries
Using subqueries in SQL is a trick that can be used to make a query dynamic or greatly decrease the execution time of a query. In this post, I’ll show you two tricks I use often to make my queries more efficient.
-
Why Indexing in SQLite will Dramatically Improve the Performance of a Query
I recently created my own SQLite database to do a one-off analysis on a special project. My database was pretty simple and had a couple of very large tables that consisted of millions of rows.
-
How to Connect Python to a Remote MySQL Database with SSH Encryption
In this post, I’ll show you how to connect Python to a remote MySQL database that has SSH encryption.
-
Optimizing Disk Usage in SQLite with the VACUUM Command
The SQLite VACUUM command is essential to run after modifying your SQLite database, especially after dropping data.
-
How to Make your Variables Dynamic in MySQL
Leveraging the GROUP_CONCAT function in MySQL allows you to concatenate query results into one row which you can then pass through as a user-defined variable in MySQL.
-
Select Everything Before or After a Delimiter in MySQL
Using the SUBSTRING_INDEX function in MySQL makes it really easy to split your string data based on a specified delimiter. This comes in handy when your string data is not uniform.
-
Cleaning up String Data in MySQL for Better Exporting
Data stored as strings can be problematic if you are exporting raw data from MySQL and using Excel or another application to analyze the data.