Using Cython, you can speed up existing Python code by an order of 100x or more. This is possible because Cython converts some of the Python code to C by doing some basic code changes. Even without any code change a speed up of 2x is commonly observed, like in this post example. Because, everything …
How to convert Python code to Cython (and speed up 100x)? Read More »
Let’s see how to cythonize Python code inside Jupyter notebooks step by step. In this post we will see how to: Define and time a Python Function to benchmark How to run Python using Cython in Jupyter Notebook Let’s cythonize the function But, let’s first answer a basic question: What is the difference between CPython …
How to convert Python to Cython inside Jupyter Notebooks? Read More »
At its core, KL (Kullback-Leibler) Divergence is a statistical measure that quantifies the dissimilarity between two probability distributions. Think of it like a mathematical ruler that tells us the “distance” or difference between two probability distributions. Remember, in data science, we’re often working with probabilities – the chances of events happening. So, if we have …
KL Divergence – What is it and mathematical details explained Read More »
The Probe method is a highly intuitive approach to feature selection. If a feature in the dataset contains only random numbers, it is not going to be a useful feature. Any feature that has lower feature importance than a random feature is suspicious. In this one, we will see: What is the Probe Method for …
Probe Method – How to select features for ML models Read More »
Problem You need to retrieve a list of dates between two specified dates. Input First, let’s create a table and insert some sample data into it. startDate endDate 2023-01-01 2023-01-05 2023-02-01 2023-02-03 For this example, the table contains two date ranges: Jan 1 to Jan 5 and Feb 1 to Feb 3 of 2023. Try …
How to get a list of dates between two dates in SQL? Read More »
The difference between CROSS JOIN and INNER JOIN in SQL The question here is about the difference between a CROSS JOIN and an INNER JOIN in SQL. While both can be used to combine rows from two or more tables, they do so in distinct ways: CROSS JOIN: Combines each row of the first table …
What is the difference between CROSS JOIN and INNER JOIN in SQL? Read More »
Problem You have two tables, tableA and tableB. You want to retrieve all records from tableA that do not have a matching record in tableB based on a specific column. Input Let’s start with creating two tables and populating them with data. tableA – This will be our primary table where we’ll select data from. …
How to get records from one table that does not exist in another? Read More »
Problem How to access the “previous row” value in a SELECT statement in SQL? Input id sale_date units_sold 1 2023-01-01 10 2 2023-01-02 15 3 2023-01-03 12 4 2023-01-04 20 Try Hands-On: HERE Source Tables: Gist Desired Solution sale_date current_day_sales previous_day_sales 2023-01-01 10 2023-01-02 15 10 2023-01-03 12 15 2023-01-04 20 12 Solution 1: To …
How to access the “previous row” value in a SELECT statement in SQL? Read More »
Problem You have a database table, how to select all columns except specific columns mentioned by the user? Example Syntax: SELECT * [except columnA] FROM tabA; Input id first_name last_name email salary 1 John Doe [email protected] 50000 2 Jane Smith [email protected] 60000 3 Bob Brown [email protected] 55000 This is not possbile in native SQL databases. …
How to exclude a column using select * except column? Read More »
Problem Often when dealing with database records, you might encounter a situation where you want to group records based on a specific column but rather than aggregating the other columns using functions like SUM() or AVG(), you want to concatenate their strings. MySQL has the GROUP_CONCAT() function for this purpose. Input ID Class Name 1 …
How to use GROUP BY to concatenate strings in MySQL and SQL Server? Read More »
Problem Let’s understand the difference between UNION and UNION ALL in SQL with an example. Input Let’s create a table named fruits with columns id, name, and type. We will insert some rows into this table: id name type 1 Apple Fresh 2 Banana Fresh 3 Apple Dried 4 Banana Dried 5 Cherry Fresh Try …
What is the difference between UNION and UNION ALL in SQL? Read More »
Problem Let’s see how to find duplicate values in an SQL table. Input id name enrollment_number 1 Alice ENR123 2 Bob ENR124 3 Charlie ENR125 4 David ENR123 5 Edward ENR126 6 Frank ENR124 Try Hands-On: Fiddle Create Source tables: Code Gist Desired Output enrollment_number count ENR123 2 ENR124 2 Solution 1: Using GROUP BY …
Problem You have a table with a column where some rows contain NULL values or empty strings. You want to retrieve rows where this column has a value (i.e., not NULL and not an empty string). Input product_id product_name product_description 1 Laptop High performance gaming laptop 2 Mouse 3 Keyboard 4 Monitor 27 inch 4K …
How to return results only if value exists in SQL? Read More »
Problem You have a table with multiple records. For each distinct value in one of the columns (i.e., each ‘group by’ group), you want to retrieve the top ‘n’ rows based on some criteria. In the below example, get the top 2 entries for each sales person based on ‘amount’ column. Input id salesperson amount …
How to get top n results in each ‘group by’ group in SQL? Read More »
Problem You have a table with multiple rows of data for each unique identifier and you want to concatenate the values of these rows into a single field for each identifier. Input user_id interest 1 Reading 1 Writing 2 Painting 2 Singing 3 Traveling Try Hands-On: Fiddle Create Table: Gist Desired Output user_id concatenated_interests 1 …
How to concatenate multiple rows into one field in MySQL? Read More »
Problem How to escape a single quote in SQL Server, with a solution that is also reproducible in MySQL? For example, let’s try to replace the single quote character with a hat (^) character. Input ID Name 1 John’s Pizza 2 Mary’s Cafe 3 Alice’s Diner Try Hands-On: Fiddle Create Input Table: Gist Desired Output …
Problem What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN? Input Let’s create three example tables, employees, departments, and projects, and insert some sample data into them. SELECT * FROM EMPLOYEES; employee_id first_name last_name department_id 1 John Doe 1 2 Jane Smith 2 3 Bob Johnson 1 4 Alice Williams SELECT …
What is the difference between INNER JOIN, OUTER JOIN and FULL OUTER JOIN? Read More »
Problem How to select a random row using SQL? Input employee_id first_name last_name salary 1 John Doe 50000 2 Jane Smith 60000 3 Michael Johnson 55000 4 Emily Brown 58000 5 David Davis 62000 Try Hands-On: Fiddle Create Input Table: Gist Desired Output employee_id first_name last_name salary 5 David Davis 62000 Solution 1: Using generated …
Problem How to randomly select rows quickly from a large table in MySQL? Input employee_id first_name last_name department salary 1 John Doe HR 50000.00 2 Jane Smith Engineering 60000.00 3 Alice Johnson Finance 55000.00 4 Bob Brown Sales 52000.00 5 Eva Williams Marketing 48000.00 6 Chris Davis Engineering 62000.00 7 Sarah Wilson Finance 56000.00 8 …
How to randomly select rows from a large table in MySQL super fast? Read More »
Problem Given a table, how to efficiently convert rows to columns? So that, the records in a given row become column names and the values of the respective columns are taken from another column from the original table? Input employee_id skill_name skill_level 1 Java 4 1 SQL 3 1 Python 5 2 Java 3 2 …
How to efficiently convert rows to columns in SQL? Read More »
