Problem You have a table with multiple rows of data for each group, and you want to select the last row from each group. From the orders table below, select the first row in each group. Input order_id customer_id order_date total_amount 1 1 2023-09-01 100.50 2 2 2023-09-02 75.20 3 1 2023-09-03 50.75 4 3 […]
How to retrieve the last record in each group in SQL? Read More »
Problem From the test_scores table below, select the top 1 row in each group. For example, you have a table with data about ‘students’ test scores, and you want to find the highest-scoring student in each subject. Input student_id subject score 1 Math 90 2 Math 85 3 Math 95 1 Science 88 2 Science
How to get the top 1 row of each group in SQL? Read More »
Problem You have a table with multiple rows of data for each group, and you want to select the first row from each group. From the orders table below, select the first row in each group. Input order_id customer_id order_date product_name quantity 1 1 2023-01-01 Laptop 1 2 1 2023-01-02 Mouse 2 3 2 2023-01-03
How to select first row in each ‘Group By’ group? Read More »
Problem How to transpose columns to rows in SQL? This essentially requires reshaping the table in such a way that certain columns of a table are moved to rows. Input ProductID Attribute1 Attribute2 Attribute3 1 Red Large Cotton 2 Blue Medium Silk 3 Green Small Wool Try Hands-Om: Fiddle Create Input Table: Gist Desired Output
How to transpose columns to rows in SQL? Read More »
Problem How to select only rows with max value on a column for each distinct value in another categorical column in MySQL? From Items table, select rows with max value on the Price column for each distinct value in the Category column. Input id Category Price 1 Fruit 2.50 2 Fruit 3.00 3 Veg 1.00
How to select only rows with max value on a column? Read More »
SQL window functions exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are designed like fun puzzles, take your time and try to solve. By the end of this set, you should feel confident in solving the hardest SQL Window function problems. If you
SQL Window Function Exercises and Solutions – Set 2 Read More »
SQL window functions is considered a ‘hard’ concept in SQL. This set of exercises is designed to challenge your SQL muscle and help internalize data wrangling using window functions in SQL. The questions are of three levels of difficulty: Easy, Intermediate and Hard. If you haven’t read the tutorial yet, read SQL Window Functions –
SQL Window Functions Exercises – Practice to perfection Read More »
SQL window functions is one of the advanced concepts, understanding which will give you the ability to do complex data wrangling and transformations in SQL. In this guide, we will intuitively understand how window functions work in a way you will never forget. Don’t memorize anything, just read through and you will clearly understand how
SQL Window Functions – Made simple and intuitive Read More »
SQL Subquery, also known as inner query or nested query, are used to query data from one or more tables and then used in another SQL statement. They are an integral part of SQL since they allow complex database queries to be handled in a simpler way. A subquery can be used in the SELECT,
SQL Subquery – How to write subqueries using simple examples Read More »
SQL, short for Structured Query Language is a programming language used to communicate with databases and do various types of Data wrangling operations. This is an essential skillset for any type of Data related jobs. In this tutorial, let’s get started with the basics of SQL. 1. Why you should learn SQL? First, let’s understand
SQL Tutorial – A Simple and Intuitive Guide to the Structured Query Language Read More »
Cook’s distance is a measure computed to measure the influence exerted by each observation on the trained model. It is measured by building a regression model and therefore is impacted only by the X variables included in the model. What is Cooks Distance? Cook’s distance measures the influence exerted by each data point (row /
Cook’s Distance for Detecting Influential Observations Read More »
Z score, also called as standard score, is used to scale the features in a dataset for machine learning model training. It can also be used to detect outliers. In this one, we will first see how to compute Z-scores and then use it to detect outliers. How is Z-score used in machine learning? Now,
How to detect outliers with z-score Read More »
Z score is one of the most important concepts in statistics. It is also called standard score. Typically it is used to scale the features for machine learning. But can also be used to detect outliers. Also Read: How to detect outliers with IQR and Box Plots How is Z-score used in machine learning? Now,
How to detect outliers using Z score? Read More »
Let’s understand what are outliers, how to identify them using IQR and Boxplots and how to treat them if appropriate. 1. What are outliers? In statistics, outliers are those specific data points that differ significantly from other data points in the dataset. There can be various reasons behind the outliers. It can be because of
How to detect outliers using IQR and Boxplots? Read More »
Pip is a widely used package manager for Python, allowing you to install and manage Python packages easily. In this blog post, we’ll explore various methods to install Pip on MacOS. I’ll provide clear, reproducible code examples for each method, making it easy for you to get started with Pip on your MacOS system. Using
install pip mac – How to install pip in MacOS?: A Comprehensive Guide Read More »
Web scraping is the technique of extracting data from a specific website or web page. This has wide applications in: Research and publication purposes Competitor and market studies Creating data for machine learning models The extracted data can be stored in any format be it a csv, txt, json, API etc so that it can
Scrapy vs. Beautiful Soup: Which is better for web scraping? Read More »
1. What is the purpose of adding Python to the PATH environment variable? Adding Python to the PATH environment variable in Windows allows you to run Python commands from any directory within the command prompt. Here are the steps to add Python to the PATH variable: 2. What is the PATH environment variable in Windows?
add Python to PATH – How to add Python to the PATH environment variable in Windows? Read More »
MICE Imputation, short for ‘Multiple Imputation by Chained Equation’ is an advanced missing data imputation technique that uses multiple iterations of Machine Learning model training to predict the missing values using known values from other features in the data as predictors. What is MICE Imputation? You can impute missing values by predicting them using other
MICE imputation – How to predict missing values using machine learning in Python Read More »
conda is a popular package management system that allows you to create isolated environments with different versions of packages and dependencies. In this one, let’s see how to view a list of all virtual environments in conda. How to list all the virtual Environments? Earlier you saw everything about creating conda environments. If you want
conda list environments – How to view all the virtual environments present in conda? Read More »
conda is a popular package management system that allows you to create isolated environments with different versions of packages and dependencies. Earlier we saw how to create a new conda environment and steps to manage it. However, if you no longer need an environment, you can delete it to free up disk space and simplify
