Sitemap

SQL LEETCODE

33 min readJul 19, 2023

How to create SQL table in production environment

Let me start with the command itself , then I would be explaining the table commands line by line.

CREATE TABLE if not exists `abc_rewards` (
`reward_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`offer_id` int(6) unsigned NOT NULL,
`reward_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`status` tinyint(1) DEFAULT '0',
`expiry_date` datetime(6) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified_by` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`hash_code` varchar(255) DEFAULT NULL,
`file_id` int(6) DEFAULT NULL,
PRIMARY KEY (`reward_id`),
KEY `index_ abc_rewards ` (`offer_id`),
KEY `idx_offer_status_ abc_rewards ` (`status`,`offer_id`,`expiry_date`),
KEY `idx_modified_ abc_rewards ` (`modified_at`),
KEY `idx_expiry_date_offer_status_ abc_rewards ` (`expiry_date`,`offer_id` ,`status`),
CONSTRAINT `index_ abc_rewards ` FOREIGN KEY (`offer_id`) REFERENCES `partner_offers` (`offer_id`));
CREATE TABLE if not exists abc_rewards(: This line starts the SQL command to create a table named "abc_rewards". The "if not exists" clause ensures that the table is only created if it doesn't already exist.
2. reward_id int(11) unsigned NOT NULL AUTO_INCREMENT: This line defines the first column of the table named "reward_id". It is of type "int" with a length of 11 digits, unsigned (positive values only), and cannot be null. The "AUTO_INCREMENT" attribute means that the column's value will be automatically incremented for each new row inserted.
3.offer_id int(6) unsigned NOT NULL: This line defines the second column of the table named "offer_id". It is of type "int" with a length of 6 digits, unsigned, and cannot be null.
4.reward_code varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL: This line defines the third column of the table named "reward_code". It is of type "varchar" with a maximum length of 255 characters. The "CHARACTER SET utf8" specifies the character set for the column, and "COLLATE utf8_unicode_ci" defines the collation for string comparison. The column cannot be null.
5.status tinyint(1) DEFAULT '0': This line defines the fourth column of the table named "status". It is of type "tinyint" with a length of 1 digit, which can represent values from -128 to 127. The column has a default value of '0'.
6.expiry_date datetime(6) DEFAULT NULL: This line defines the fifth column of the table named "expiry_date". It is of type "datetime" with a precision of 6 digits for fractional seconds. The column has a default value of NULL.
7.created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP: This line defines the sixth column of the table named "created_at". It is of type "timestamp" and represents a date and time value. The column cannot be null, and its default value is the current timestamp when a row is inserted.
8.modified_by varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL: This line defines the seventh column of the table named "modified_by". It is of type "varchar" with a maximum length of 255 characters. The "CHARACTER SET utf8" specifies the character set for the column, and "COLLATE utf8_unicode_ci" defines the collation for string comparison. The column cannot be null.
9.modified_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: This line defines the eighth column of the table named "modified_at". It is of type "timestamp" and represents a date and time value. The column cannot be null, and its default value is the current timestamp when a row is inserted or modified.
10.hash_code varchar(255) DEFAULT NULL: This line defines the ninth column of the table named "hash_code". It is of type "varchar" with a maximum length of 255 characters. The column has a default value of NULL.
11.file_id int(6) DEFAULT NULL: This line defines the tenth column of the table named "file_id". It is of type "int" with a length of 6 digits. The column has a default value of NULL.
12.PRIMARY KEY (reward_id): This line specifies that the "reward_id" column is the primary key of the table. The primary key uniquely identifies each
13.KEY index_abc_rewards (offer_id),: This line defines an index on the "offer_id" column of the table. An index improves the performance of queries that involve searching or sorting based on the "offer_id" column.
14.KEY idx_offer_status_abc_rewards (status,offer_id,expiry_date),: This line defines an index on three columns: "status", "offer_id", and "expiry_date". The order of columns in the index matters and can affect the performance of queries. This index can be used efficiently for queries that involve filtering or sorting based on these three columns.
15.KEY idx_modified_abc_rewards (modified_at),: This line defines an index on the "modified_at" column of the table. This index can improve the performance of queries that involve sorting or filtering based on the "modified_at" column.
16.KEY idx_expiry_date_offer_status_abc_rewards (expiry_date,offer_id,status),: This line defines an index on three columns: "expiry_date", "offer_id", and "status". The order of columns in the index matters and can affect the performance of queries. This index can be used efficiently for queries that involve filtering or sorting based on these three columns.
17.CONSTRAINT index_abc_rewards FOREIGN KEY (offer_id) REFERENCES partner_offers (offer_id): This line defines a foreign key constraint on the "offer_id" column of the "abc_rewards" table. It specifies that the values in the "offer_id" column must match the values in the "offer_id" column of the "partner_offers" table. This constraint ensures referential integrity between the two tables.

What is Datalake and how it is different from Datawarehouse?

A data lake and a data warehouse are two different approaches to storing and managing data. Here’s a graphical representation that illustrates the key differences between them:

Data Lake:

A data lake is a storage repository that holds vast amounts of raw and unprocessed data. It can handle structured, semi-structured, and unstructured data from various sources, such as logs, social media, sensors, etc. The data is stored in its native format, without any predefined organization or schema. The key components of a data lake include the data store, data processing, and data exploration.
Data Store: The data store in a data lake is designed to efficiently store large volumes of data. It may use distributed file systems like Hadoop Distributed File System (HDFS) or cloud-based storage solutions.
Data Processing: Data processing in a data lake involves transforming and preparing the raw data for analysis. This can include data cleaning, filtering, aggregation, and enrichment.
Data Exploration: Data exploration involves performing ad-hoc analysis on the raw data to gain insights and discover patterns. This can be done using tools like Apache Spark, Apache Hive, or Presto.
+ - - - - - - - - - - - - - - - - - +
| Data Lake |
+ - - - - - - - - - - - - - - - - - +
| Raw and Unstructured Data |
| |
| + - - - - - - - - -+ |
| | Data Store | |
| + - - - - - - - - -+ |
| |
| + - - - - - - - - -+ |
| | Data Processing | |
| + - - - - - - - - -+ |
| |
| + - - - - - - - - - - -+ |
| | Data Exploration | |
| + - - - - - - - - - - -+ |
+ - - - - - - - - - - - - - - - - - +

Data Warehouse:

A data warehouse, on the other hand, is a structured and processed data storage system that is optimized for business intelligence and reporting. It integrates data from different sources, organizes it into a consistent and structured format, and applies predefined schemas for efficient querying and analysis. The main components of a data warehouse include the data warehouse itself, data processing, and business analytics.
Data Warehouse: The data warehouse acts as the central repository for structured and processed data. It follows a predefined schema and data model optimized for querying and reporting.
Data Processing: Data processing in a data warehouse involves extracting, transforming, and loading (ETL) the data from various sources into the warehouse. This includes cleaning, aggregating, and transforming the data to match the warehouse schema.
Business Analytics: Business analytics involves using tools like reporting and visualization platforms to analyze the structured data and generate insights for decision-making.
| Data Warehouse |
+ - - - - - - - - - - - - - - - - - +
| Structured and Processed |
| Data |
| |
| + - - - - - - - - -+ |
| | Data Warehouse | |
| + - - - - - - - - -+ |
| |
| + - - - - - - - - -+ |
| | Data Processing | |
| + - - - - - - - - -+ |
| |
| + - - - - - - - - - - -+ |
| | Business Analytics | |
| + - - - - - - - - - - -+ |
+ - - - - - - - - - - - - - - - - - +

In summary, a data lake is a repository for raw and unstructured data, focusing on flexibility and scalability. It enables data exploration and analysis on the original data format. On the other hand, a data warehouse is a structured and processed data storage system optimized for business intelligence and reporting, providing a consistent and structured view of the data.

Tools used in Data Warehouse and Data Lake:

Data Lake Tools:

Apache Hadoop: An open-source framework that provides distributed processing and storage capabilities for big data. It includes components like Hadoop Distributed File System (HDFS) for storage and Apache Spark for data processing.
Apache Hive: A data warehouse infrastructure built on top of Hadoop, allowing users to query and analyze data stored in Hadoop using a SQL-like language called HiveQL.
Apache Kafka: A distributed streaming platform that enables the ingestion of real-time data into the data lake, making it suitable for streaming and event-driven architectures.
AWS S3: Amazon Simple Storage Service (S3) is a scalable object storage service provided by AWS, commonly used as a data lake storage solution due to its durability, availability, and scalability.
Azure Data Lake Storage: A cloud-based storage service provided by Microsoft Azure that allows organizations to store and analyze large amounts of data in a cost-effective manner. It integrates well with other Azure services like Azure Databricks and Azure Synapse Analytics.

Data Warehouse :

Microsoft SQL Server: A popular relational database management system (RDBMS) that includes features specifically designed for data warehousing, such as SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).
Oracle Database: An enterprise-grade RDBMS with a comprehensive set of tools and features for data warehousing, including Oracle Data Integrator (ODI) and Oracle Business Intelligence (OBIEE).
IBM Db2 Warehouse: A data warehousing solution that provides high-performance analytics and integrates with other IBM tools like Cognos Analytics for reporting and visualization.
Snowflake: A cloud-based data warehouse that offers scalability, performance, and ease of use. It supports various integrations and provides native support for SQL querying.
Amazon Redshift: A fully managed, petabyte-scale data warehouse service provided by Amazon Web Services (AWS), optimized for online analytic processing (OLAP) workloads.

Sql Queries to use in day to day daily tasks.

To add the values in the code.

Query :

update db_name.properties
set column_a = concat(ifnull(val,""), ',143,175')
where column_b = 'asd';
Description:The provided query appears to be an SQL statement written to update a table in a database. It seems that you want to update the column_a of the table db_name in the properties schema.
The SET clause in the query indicates that you want to modify the value of column_a. It uses the concat function to concatenate the existing value of column_a with a string that includes the result of ifnull(val, "") (which is either the value of val if it's not null, or an empty string if it is null), followed by the strings ",143,175".
The WHERE clause specifies the condition for which rows should be updated. In this case, it checks if the value of column_b is equal to 'asd'.
Please note that this is a general explanation, and the actual execution and result of the query may depend on the specific database management system (e.g., MySQL, PostgreSQL, Oracle) and the structure of your database schema.

SQL BASICS

Date_sub me intervsl aata hain , Datediff m do dates aati hain jinka difference aa raha hot hain ,

t1.recordate = DATE_SUB(t2.recordate, INTERVAL 1 DAY)

where datediff(w1.recordDate,w2.recordDate)=1

datediff(day, created_at, resolved_at)

datediff(week, created_at, resolved_at)

datepart(weekday,holiday_date) not in 1,7

string_split(‘whole word in this will be splitted’ ‘ , ’) according to the comma

CROSS APPLY string_split(column_name, ‘,’)

DATE_FORMAT(trans_date, '%Y-%m',

TO_CHAR(trans_date, ‘YYYY-MM’)

DATE_ADD AND DATEDIFF

SUBSTR(CREATED_aT,1,16)

MUST QUESTION FOR JOIN:

there is one column id feom tabl1 with values  1,1,1,0,0,null,null ND OTHER TABLE WITH VALUES 1,1,0, NULL HOW MANY INNER LEFT RIGHT AND OUTER JOINS ARE THERE?

Let's break down the types of joins you mentioned—INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN—between the two tables based on their id columns and the given values.

Table 1 (id column):
arduino
Copy code
1
1
1
0
0
NULL
NULL
Table 2 (id column):
arduino
Copy code
1
1
0

1. INNER JOIN:
INNER JOIN returns only the matching values between both tables, excluding NULLs.
Matching values: 1, 0 (NULLs are excluded).
Therefore, the result of the INNER JOIN is the rows with 1 and 0 that exist in both tables.
Number of matching rows:

1 appears 3 times in Table 1 and 2 times in Table 2. This results in
3
×
2
=
6
3×2=6 combinations for 1.
0 appears 2 times in Table 1 and 1 time in Table 2. This results in
2
×
1
=
2
2×1=2 combinations for 0.
Total INNER JOIN result: 6 + 2 = 8 rows.

2. LEFT JOIN:
LEFT JOIN returns all rows from Table 1 and the matching rows from Table 2. If no match is found, it returns NULL from Table 2.
Matching values: 1, 0, and also include NULLs from Table 1.
For 1 and 0, the matches are the same as in the INNER JOIN. However, Table 1 has 2 NULL values that don't match with any values in Table 2, so they will be paired with NULLs.

Total LEFT JOIN result: 8 (INNER JOIN matches) + 2 NULLs from Table 1 = 10 rows.

3. RIGHT JOIN:
RIGHT JOIN returns all rows from Table 2 and the matching rows from Table 1. If no match is found, it returns NULL from Table 1.
Matching values: 1, 0, and include NULLs from Table 2.
For 1 and 0, the matches are the same as in the INNER JOIN. Table 2 has 1 NULL value that doesn't match any values in Table 1, so it will be paired with NULL from Table 1.

Total RIGHT JOIN result: 8 (INNER JOIN matches) + 1 NULL from Table 2 = 9 rows.

4. FULL OUTER JOIN:
FULL OUTER JOIN returns all rows from both tables, with matching rows where possible, and NULL where no match is found.
This includes all 1s, 0s, and all NULL values from both tables.
Total FULL OUTER JOIN result:

8 (INNER JOIN matches),
2 unmatched NULLs from Table 1,
1 unmatched NULL from Table 2.
Total FULL OUTER JOIN result: 8 + 2 + 1 = 11 rows.

Summary of Join Results:
INNER JOIN: 8 rows
LEFT JOIN: 10 rows
RIGHT JOIN: 9 rows
FULL OUTER JOIN: 11 rows

GROUP BY

Suppose we have a table named “students” with columns “name”, “class”, and “marks”. We want to find out the total marks obtained by each student in each class.

SELECT name, class, SUM(marks) as total_marks
FROM students
GROUP BY name, class;

PARTITION BY:

Suppose we have a table named “students” with columns “name”, “class”, and “marks”. We want to find out the total marks obtained by each student in each class along with the average marks obtained by all students in that class.

ye dekha jaye to bhaut sahi hai ki hume kis basis pe avg ya sum chahiye

-------using window function------------------
SELECT
s.name,
s.class,
SUM(s.marks) AS total_marks,
AVG(s.marks) OVER (PARTITION BY s.class) AS average_marks_in_class
FROM
students s
GROUP BY
s.name, s.class
ORDER BY
s.class, s.name;

or
-------using self join------------------------------------------
SELECT
s.name,
s.class,
SUM(s.marks) AS total_marks,
avg_class_marks.avg_marks AS average_marks_in_class
FROM
students s
JOIN
(SELECT
class,
AVG(marks) AS avg_marks
FROM
students
GROUP BY
class
) avg_class_marks
ON
s.class = avg_class_marks.class
GROUP BY
s.name, s.class, avg_class_marks.avg_marks
ORDER BY
s.class, s.name;


---------using subquery-------------------------
SELECT
s.name,
s.class,
SUM(s.marks) AS total_marks,
(SELECT AVG(marks)
FROM students
WHERE class = s.class) AS average_marks_in_class
FROM
students s
GROUP BY
s.name, s.class
ORDER BY
s.class, s.name;

Example 1 find the id ,date , temp of columns where the temp wa high from prev day.

lag() function is exactly like row_number function.

  1. join on date
  2. lag over
  3. subquery
Press enter or click to view image in full size

output :

ON t1.recordate = DATE_SUB(t2.recordate, INTERVAL 1 DAY)

DATE_SUB(NOW(), INTERVAL '6' HOUR)
SUBSTR(CREATED_aT,1,16)
GROPU BY HAVING CONDITION USE ...group_concat(distinct order_id)
Date_sub
SELECT t1.id, t1.recordate, t1.temperature
FROM YourTable t1
JOIN YourTable t2 ON t1.recordate = DATE_SUB(t2.recordate, INTERVAL 1 DAY)
WHERE t1.temperature > t2.temperature;
Dateddif
Select w1.id
from Weather w1,Weather w2
where datediff(w1.recordDate,w2.recordDate)=1 and w1.temperature > w2.temperature;
lagWITH PreviousDay AS (
SELECT id, recordate, temperature,
LAG(temperature) OVER (ORDER BY recordate) AS previous_temperature
FROM YourTable
)
SELECT id, recordate, temperature
FROM PreviousDay
WHERE temperature > previous_temperature;
Press enter or click to view image in full size

Example 2:

make a sql query points for each win 2 points :

1ST: USING CASE METHOD:

note:then 1….this number after then will become the column value in case satement

Press enter or click to view image in full size
SELECT team_name, 
COUNT(*) AS total_matches,
SUM(CASE WHEN team_name = winner THEN 1 ELSE 0 END) AS total_wins,
SUM(CASE WHEN team_name = winner THEN 2 ELSE 0 END) AS points
FROM (
SELECT team1 AS team_name, winner
FROM matches
UNION ALL
SELECT team2 AS team_name, winner
FROM matches
) AS combined_matches
GROUP BY team_name
ORDER BY points DESC;

2ND : USING SUBQUERIES:

SELECT 
team_name,
(SELECT COUNT(*) FROM matches WHERE team1 = team_name OR team2 = team_name) AS total_matches,
(SELECT COUNT(*) FROM matches WHERE winner = team_name) AS total_wins,
(SELECT COUNT(*) * 2 FROM matches WHERE winner = team_name) AS points
FROM
(SELECT team1 AS team_name FROM matches
UNION ALL
SELECT team2 AS team_name FROM matches) AS team_data
GROUP BY team_name
ORDER BY points DESC;

3RD : USING CTE

WITH team_matches AS (
SELECT team1 AS team_name, winner
FROM matches
UNION ALL
SELECT team2 AS team_name, winner
FROM matches
)
SELECT team_name,
COUNT(*) AS total_matches,
SUM(CASE WHEN team_name = winner THEN 1 ELSE 0 END) AS total_wins,
SUM(CASE WHEN team_name = winner THEN 2 ELSE 0 END) AS points
FROM team_matches
GROUP BY team_name
ORDER BY points DESC;

Example 3: again, do it either with join or lag

SELECT
t1.dept_id,
t1.quarter,
t1.total_sales,
(t1.total_sales - t2.total_sales) / t2.total_sales * 100 AS growth_percentage
FROM
sales_table t1
JOIN
sales_table t2 ON t1.dept_id = t2.dept_id
AND t1.quarter = CONCAT('Q', (SUBSTRING(t2.quarter, 2) + 1))
SELECT
dept_id,
quarter,
total_sales,
CASE
WHEN LAG(total_sales) OVER (PARTITION BY dept_id ORDER BY quarter) IS NULL THEN 0
ELSE (total_sales - LAG(total_sales) OVER (PARTITION BY dept_id ORDER BY quarter)) / LAG(total_sales) OVER (PARTITION BY dept_id ORDER BY quarter) * 100
END AS growth_percentage
FROM
your_table
ORDER BY
dept_id, quarter;
Press enter or click to view image in full size
SELECT
dept_id,
quarter,
total_sales,
CASE
WHEN LAG(total_sales) OVER (PARTITION BY dept_id ORDER BY quarter) IS NULL
ELSE (total_sales - LAG(total_sales) OVER (partition by dept_id order by quarter)/ LAG(total_sales) OVER (partition by dept_id order by quarter) * 100

…………………Coalesce………………………..

To ignore null or replace null values with 0:

Find the names of the customer that are not referred by the customer with id = 2.
SELECT name
FROM Customer
WHERE COALESCE(referee_id,0) <> 2;

…………… character length……………………..

select tweet_id from Tweets where length(content) > 15

— — — — — — — — null join — — — — — — — — — -

if you need null at the places of unique id do left join instead of join

Input: 
Employees table:
+----+----------+
| id | name |
+----+----------+
| 1 | Alice |
| 7 | Bob |
| 11 | Meir |
| 90 | Winston |
| 3 | Jonathan |
+----+----------+
EmployeeUNI table:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3 | 1 |
| 11 | 2 |
| 90 | 3 |
+----+-----------+
Output:
+-----------+----------+
| unique_id | name |
+-----------+----------+
| null | Alice |
| null | Bob |
| 2 | Meir |
| 3 | Winston |
| 1 | Jonathan |
+-----------+----------+
wrong
select b.unique_id,a.name from Employees a join EmployeeUNI b on a.id =b.id
right
select b.unique_id,a.name from Employees a left join EmployeeUNI b on a.id =b.id

— — — — -join+group by a.customer_id — — — — — —

confusion here is which column to select for group by

output me hi customer id hai aur uska count kar rakha hai condition ke according.

case lene ki zarurat nahi hai join aise karo ki ussi row me start time bhi aajye aur end type bhi aa jaye…although ye case se bhi ho sakte hai….

select a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) as processing_time 
from Activity a1
join Activity a2
on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
and a1.activity_type='start' and a2.activity_type='end'
group by a1.machine_id

— — — — — best example of join+group by — — — — —

Write a SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.

Input: 
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |
+----------------+----------+--------+
Output:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 | 2 |
| 30 | 1 |
| 96 | 1 |
+-------------+----------------+

isme join 2 table se kiya left join kiya hai left ki jo vlue right me missing hai waha pe sare column ki value null aayegi to ussi use karke group by kar liya hai …..intelligent move

select a.customer_id , count(*) as count_no_trans 
 from Visits a left join Transactions b on a.visit_id = b.visit_id where b.amount is null
group by a.customer_id

delete duplicate rows: 3 methods

1.ROW_INDEX:

PARTITION BY ID AS WELL AS ORDER BY ID

WITH CTE AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn
FROM your_table
)
DELETE FROM CTE WHERE rn > 1;

2: Using GROUP BY and HAVING

ISME BHI SQUBQUERY LAGEGI

DELETE FROM your_table
WHERE id IN (
SELECT id
FROM your_table
GROUP BY id
HAVING COUNT(id) > 1
);

3.: Using a Self-Join

use delete in place of select

this is wrong as it is based on a.id=b.id.The id in your dataset is unique for each row (since it's a primary key or identifier), so a.id = b.id in the JOIN will never match across different rows, and therefore won't correctly identify duplicates


DELETE a
FROM your_table a
JOIN your_table b ON a.id = b.id
WHERE a.rowid > b.rowid;
DELETE a
FROM your_table a
JOIN your_table b
ON a.salary = b.salary
WHERE a.rowid > b.rowid;

EXPLANATION ON SELF JOIN

DATASET:

+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | Alice | 5500 |
| 2 | Bob | 5000 |
| 3 | Carol | 5500 |
+----+-------+--------+
Without the a.id > b.id condition, 
the self-join would give you two pairs for records with a salary of 5500:
a_id | b_id | a_name | a_salary
1 | 3 | Alice | 5500
3 | 1 | Carol | 5500

With the a.id > b.id condition, you get only one pair, avoiding duplication:

ISME HUMNE PRIORITY ASCENDING ORDER KO DI HAI

a_id | b_id | a_name | a_salary
1 | 3 | Alice | 5500

ok , here is one of the example where data is kept in column and we have to make it row…

join se ek aur faayda hai jo mujhe dikkat aa rahi thi ki 2 column se group karna padega ab uska solution hai ki join 2 columns pe laga do fir ek group by karo

select a1.machine_id
, ROUND(AVG(a2.timestamp - a1.timestamp),3) as processing_time
from Activity a1
join Activity a2
on a1.process_id=a2.process_id
and a1.machine_id=a2.machine_id
and a1.timestamp<a2.timestamp
group by a1.machine_id;
select a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3)
as processing_time
from Activity a1
join Activity a2
on a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
and a1.activity_type='start' and a2.activity_type='end'
group by a1.machine_id

isme case wala use nhi ho payega kuonki null hoga parallel me to we count minus both….but neeche wo kaam kar gaya having condition me sara daal ke

Question:swimmers who won only gold medals?

union All

1.swimmers who won only gold medals.

Press enter or click to view image in full size

This is important: convert the row into one column …intelligent

— — — — — — — — — — — — — — — — — — — — — — — — 2. To calculate the business days from all days.

logic of calculating business days:days — 2*weeks

Also left join on….between

Press enter or click to view image in full size

lets join this with holiday table

Press enter or click to view image in full size
Press enter or click to view image in full size

what if the public holiday is on a weekend.

DATEPART(weekday, holiday_date) not in (1,7)

select *, DATEDIFF(day,create_date, resolved_date) — 2*DATEDIFF(week,create_date, resolved_date) — holiday as bdays from (select t.*, count(holiday_date) as holiday from tickets t left join holidays h on h.holiday_date between t.create_date and t.resolved_date and DATEPART(weekday, holiday_date) not in (1,7) group by ticket_id, create_date, resolved_date) A

— — — 3. in and out ..who is in at the office — — — —

case ke case me max lagana padega nahi to group by me daalna padega

crux ye bhi hai ki group by empid kar rahe hai taki dono case statement aamne saamne aa jaye

Press enter or click to view image in full size
Press enter or click to view image in full size

2nd method:

case kiya tha upar

yaaa ek part intime ka bana diya dusra outtime ka dono ko join kar diya

with wali command ke sath join se karo agar 2 table use kar rah ho to

its a problem to use inner join

kuonki usme matching wale layega sirf, un ka kya jo null hai kuonki wi in to hue hai out nahi to unka outtime wali tavble to null hai isliye we will use left join

3r method:

— — — — — — — — 4th example — — — — — — — - :

5th example:

6th Example:

Press enter or click to view image in full size

example 7:

example 8:

Press enter or click to view image in full size

maximum records:

minimum records:

agar maximum karwana hai to dono ko match kara do

Get Ankit Juneja’s stories in your inbox

Join Medium for free to get updates from this writer.

minimum karna hai to dono ko bilkul alag alag

example 9:

example 10:

finf top 10 and link it with artist id

example 11:

ye acha answer hai jab pure column ko comapare karana hai max ya min salry se to cse statement lagayi

other method:

get a rank:

Press enter or click to view image in full size

example 12:

Press enter or click to view image in full size
Press enter or click to view image in full size
Press enter or click to view image in full size
Press enter or click to view image in full size
Press enter or click to view image in full size
Press enter or click to view image in full size

example 13:

puzzle

Press enter or click to view image in full size

example 14:

question wise.

first order table

2nd question

which category and subcategory has ifhest returned sales

example 15:

oldest with yongest:

leet codes:

ek row ko dusri adjacent row me dudhna hai to subquery use ki

isme main exists use kar raha tha par usse kaam nahi chala

last me in se hi kaam chala

Press enter or click to view image in full size

case me koi bracket nahi hogi and multiple statement with and

Press enter or click to view image in full size
SELECT x, y, z, CASE WHEN x + y > z  and y+z>x and z+x>y
THEN 'Yes' ELSE 'No' END AS triangle
FROM Triangle ;

leetcode -1 — — — — — — — — — — — — — — — — — — — — — — —

Press enter or click to view image in full size

ok here first i learned regexp because like static ke liye hota hai aur hume regexp chahiye

select user_id,name,mail from Users where
mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com';

first thing ^[] start kaha se ho raha hai ye dhyan rakhna hai

second thing dusre pattern me jo bhi eligible hai daal do without any backslash kuonki regexp tabhi to use kiya hai aur ek aur baat []* star pe end kar do ye taaki continue hota rahe

leet code fix chahoye tha to wo bhi ek pattern ki tarah regexp me aa gaya

usme dot aaya tha usko bhi ya to escape se ya regexp se sort kar diya

leetcode — — — — -2 — — — — — — — — — — — — — — — — -

here one thing i was about to make other column but then group by se sum kar sakte hai without having another column …also specific month and specific year e liye thoda struggle hua hai ki month as integer retuen karega ya name but theek raha having condition bhi sahi se use ki

select a.product_name,sum(unit) as unit 
from Products a join Orders b on a.product_id = b.product_id
and month(order_date) = 2 and year(order_date)= 2020
group by b.product_id having sum(unit)>=100

leetcode — — — — — — — — — — — — — — -3

jo where clause me column us kiya hai wo to aa hi nahi raha

Therefore subquery is must in this case.

you cannot use a subquery without an aliasses

but row_number to sirf number aayega hume to salary print karwani hai

what about null case?

SELECT IFNULL(
(SELECT MAX(salary)
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee)),
NULL
) AS 'SecondHighestSalary';




select ifnull(
(select distinct salary
from Employee
order by salary desc
limit 1,1),
null) as 'SecondHighestSalary'



SELECT IFNULL(
(SELECT salary
FROM (SELECT salary, rank() over (ORDER BY salary DESC) as rn FROM Employee) a
WHERE a.rn = 2),
NULL
) AS 'SecondHighestSalary';

leetcode — — -4 — — — — — — — — — — — — — — — — — -

the main issue is distinct nahi lagaya tha

by default SEPARATOR ‘, ’ ye default hai to koi dikkat nahi hui

select 
sell_date,
count(distinct(product)) as num_sold,
GROUP_CONCAT(distinct(product) SEPARATOR ',') as products
from Activities
group by sell_date
order by sell_date, product;

leetcode — — — -5 — — — — — — — — — — — — — — — — — — — — — — — — — —

remeber when we joining the tables it will look like this

delete p1  from Person p1 join Person P2
on p1.email = p2.email and p1.id>p2.id

isse ek aur chiz seekhne ko mili ki jis column ko delete karna hai ussi se match karo and then delete command chalegi uspe jispe wo match kar rahi hai…..matlab final result hume join table ka nahi dekhna balki main original table ka dekhna hai

leetcode 6 — — — — — — — — — — — — — — — — — — -

isme ab kya akrna hai ki use case pass karane the aur use case me saare hi pass ho gaye bas ek reh gaa ki wo word jjahan bhi aaye line me start wahi se hona chahiye tabhi to prefix lagaya hai

lekin space dedo usko kya pata kitna space hai wo space se kaam ho jayega

select patient_id,patient_name,conditions from Patients
where conditions like "DIAB1%" OR CONDITIONS LIKE '% DIAB1%';

leetcode 7 — — — — — — — — — — — — — — — — — — — — — -

agar lag raha hai ki group by me do column daalne padenge toek baar aur socho ki jo count ka hai use count me rakhte hain upar selec ke sath and jo group by me daalna hai uso ek hi rakhte hain

results agar lexicographically bola hai to simple uss column ko ASC kar do aur agar ek hi bola hai to limit 1 bhi kar do

group by me bhi alias aayega …m.movie also usme desc asc nahi aata

ek to union all seekha dusra group by and order by limit 1 that too

feb month bola hai to zarurui nahi hai month filter hi lagana hai

leet code 8 — — — — — — — — — — — — — — — — — — — — — — — — — — -

pehli baat ifnull ke baad 2 bracket aayegi aur fir se select se start hoga

dusri baat jab bhi subquery le rahe ho please use alias

leetcode — — 9 — — — — — — — — — — — — — -

how to take only first year in a joined column , now getting the error what to do in this case

# Write your MySQL query statement below
select
product_id, year as first_year, quantity, price
from Sales
where(product_id, year) in (select product_id, min(year) from Sales group by product_id)

but hume min wali nahi first wali chahiye …which is i think same here ….let me read th question gaain

ideally hona rank se chahiye ki first wali aaye let me build the query ….in thiss case use dense_rank

ab isme partition by compulsory hai kuonki ek partition ke first year lena hai to partition lena compulsory hai

remember to alias the dense_rank

with cte as (isme select saare column kar do aur sath me dense rank wala bhi alais karke fir neeche jaake usse use karo

# Write your MySQL query statement below
/**
Two Tables: Sales/Product

(sale_id, year) is the primary key of Sales table.
product_id is a foreign key to Product table.
Sales table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

product_id is the primary key of Product table.
Product table indicates the product name of each product.

PROBLEM: selects the product id, year, quantity, and price for the first year of every product sold
*/
#First Solution

WITH CTE AS
(
SELECT
product_id,
MIN(year) AS first_year
FROM Sales
GROUP BY 1
)
SELECT
product_id,
year AS first_year,
quantity,
price
FROM Sales
WHERE (product_id, year) in (select * from CTE)

#Second Solution

WITH CTE AS
(
SELECT
product_id,
year,
quantity,
price,
dense_rank() over(partition by product_id order by year asc) as rnk
FROM Sales
)

SELECT
product_id,
year AS first_year,
quantity,
price
FROM CTE where rnk = 1

leetcode 10 — — — — —

how to calculate this…maine hardfix kar diya hai ki 2 but nahi hardfix nahi karna

also count se matlab hai hume andar ki values copare nahi karani to kya kare

leetcode 11 — — — — — — — — — — — — — — — — — — — — —

datediff(first date, second date) and here first date-second date hoti hai

select activity_date as date

Press enter or click to view image in full size
SELECT activity_date as day, COUNT(DISTINCT user_id) as active_users
FROM Activity
WHERE datediff('2019-07-27',activity_date)<30
AND activity_date <= '2019-07-27'
GROUP BY activity_date

leetcode — — 12 — — — — — — — — — — — — — — — — — — — — — — — — —

jo percentage wala hai usme upar wala jo portion hai that is numerator that can be according to the group by column but denomiatore me e can use whole select statement it wont carry the difference.

select contest_id, round(count(distinct user_id) /(select count(user_id)
from Users)*100,2) as percentage from Register
group by contest_id order by percentage desc , contest_id

interview question asked identify the missing number.

used lead function

lead function

WITH OriginalNumbers AS (
SELECT 1 AS num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 10 UNION ALL
SELECT 14 UNION ALL
SELECT 15
)

SELECT num + 1 AS missing_number
FROM (
SELECT num,
LEAD(num) OVER (ORDER BY num) AS next_num
FROM OriginalNumbers
) AS numbered
WHERE next_num - num > 1;

next is left join

WITH Numbers AS (
SELECT 1 AS num
UNION ALL
SELECT num + 1
FROM Numbers
WHERE num < 15 -- Assuming you want to check up to the highest number in your list
)

SELECT num AS missing_number
FROM Numbers
LEFT JOIN (
SELECT 1 AS num UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 10 UNION ALL
SELECT 14 UNION ALL
SELECT 15
) AS OriginalNumbers ON Numbers.num = OriginalNumbers.num
WHERE OriginalNumbers.num IS NULL;

if the input is like a column num with values 1 2 3 5 7 8 10 14

WITH Numbered AS (
SELECT num,
LEAD(num) OVER (ORDER BY num) AS next_num
FROM YourTable -- Replace with the actual table name
)

SELECT num + 1 AS missing_number
FROM Numbered
WHERE next_num - num > 1;

leetcode 13 — — — -

ek bahut h acha lesson mila hai upar maine denominator me select statemnt chali thi aur usko group kiy tha ki wo user wise hi divide kare,,,,,,,butwo sahi nahi hai mujhe last me hi group by karna hai wo automatically numerator aur denominator ka dhyan rakhega

SELECT
a.user_id,
round(SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END) /
COUNT(a.user_id),2) AS confirmation_rate
FROM
Signups a
JOIN
Confirmations b ON a.user_id = b.user_id
GROUP BY
a.user_id;

abhi dikkat bas itni aa rahi hai ki wo 6 wali row ko output me consider nahi kar raha

left join use karu kya inner join use kar rakha hai shayad tabhi nahi aa rhaa ho.

YES,yahi issue tha

SELECT
a.user_id,
round(SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END) /
COUNT(a.user_id),2) AS confirmation_rate
FROM
Signups a
LEFT JOIN
Confirmations b ON a.user_id = b.user_id
GROUP BY
a.user_id;

leetcode 14 — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

# Write your MySQL query statement below
SELECT name FROM Employee WHERE id IN (SELECT DISTINCT managerId FROM Employee
GROUP BY managerId
HAVING managerId IS NOT NULL and COUNT(managerId)>=5);
select a.name from Employee a join 
Employee b on a. id = b.managerId group by a.id having count(a.id)>=5

leetcode 15 — — — — — — — — — — — —

round off karne se pehle 100 ko exclude karni hai in case of percentages othwerwise output galaat gaayegi

doubt ek hi jagah tha jaha maine grou by kiya hai na waha pe apne aap hi ye plus kar dega

SELECT
query_name,
ROUND(AVG(rating / position), 2) AS quality,
ROUND(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END)
/
COUNT(query_name) * 100, 2) AS poor_query_percentage
FROM
Queries
GROUP BY
query_name;

leetcode 16 — — — -

did i do something wrong in group by?

yes her self join is complusory on employye_id column and report_to column

isme join use kiya hai left join nahi dhyaan do

plus count by uss column ko kiya hai jo intersect hoke aaya hai…to null ka to case hi hat gaya..yahi maine practice me mistake kari hai

sSELECT 
e1.employee_id,
e1.name,
COUNT(e2.reports_to) AS reports_count,
ROUND(AVG(e2.age)) AS average_age
FROM Employees e1 JOIN Employees e2 ON e1.employee_id = e2.reports_to
GROUP BY e2.reports_to
ORDER BY e1.employee_id;

leetcode — — — — — — — — 17

how to average the particulare requirement

now here first see what is expected in the centre there is plus so it should start with sum

Average selling price = Total Price of Product / Number of products sold.
Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
# Write your MySQL query statement below
SELECT p.product_id, round(SUM(p.price*u.units)/sum(u.units),2) as average_price
FROM Prices p
INNER JOIN UnitsSold u
ON p.product_id = u.product_id AND
u.purchase_date BETWEEN p.Start_date and p.end_date
GROUP BY p.product_id

leetcode 18 — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Date_sub me intervsl aata hain , Datediff m do dates aati hain jinka difference aa raha hot hain ,DATE_FORMAT(trans_date, '%Y-%m',TO_CHAR(trans_date, ‘YYYY-MM’)

SELECT 
DATE_FORMAT(trans_date, '%Y-%m') AS month
, country
, COUNT(*) AS trans_count
, SUM(IF(state = 'approved', 1, 0)) AS approved_count
, SUM(amount) AS trans_total_amount
, SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country

/* Write your PL/SQL query statement below */
SELECT TO_CHAR(trans_date, 'YYYY-MM') month, country, count(*)

trans_count, SUM(CASE WHEN state='approved' THEN 1 ELSE 0 END)

approved_count, SUM(amount) trans_total_amount,

SUM(CASE WHEN state='approved' THEN amount ELSE 0 END) as

approved_total_amount FROM Transactions

GROUP BY TO_CHAR(trans_date, 'YYYY-MM'), country

leetcode 19 — — — — — —

isme abhi mujhe pata hai ki query galta hai par kya kare

group by me jo column hai that does not match

SELECT 
(COUNT(a.event_date) / (SELECT COUNT(DISTINCT player_id) FROM Activity)) AS fraction,
a.player_id,
a.device_id
FROM
Activity a
JOIN
Activity b ON a.event_date = DATE_ADD(b.event_date, INTERVAL 1 DAY)
WHERE
DATEDIFF(a.event_date, b.event_date) = 1
GROUP BY
a.player_id, a.device_id;

below is one outstanding solution

# Write your MySQL query statement below
select round( count(player_id) / (select count(distinct player_id) from activity) , 2) as fraction
from Activity
where (player_id,event_date) in
(select player_id, DATE_ADD(min(event_date), INTERVAL 1 DAY) from Activity
group by 1)
1.
SELECT ROUND(SUM(login)/COUNT(DISTINCT player_id), 2) AS fraction
FROM (
SELECT
player_id,
DATEDIFF(event_date, MIN(event_date) OVER(PARTITION BY player_id)) = 1 AS login
FROM Activity
) AS t
2.
with cte as (
select player_id, DATE_SUB(event_date, INTERVAL 1 DAY)
=min(event_date) over(partition by player_id) as is_return from activity
)
select round(sum(is_return)/count(distinct player_id), 2)
as fraction from cte

3.SELECT ROUND(COUNT(t2.player_id)/COUNT(t1.player_id),2) AS fraction
FROM
(SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id) t1 LEFT JOIN Activity t2
ON t1.player_id = t2.player_id AND t1.first_login = t2.event_date - 1
/* Write your PL/SQL query statement below */ 
SELECT ROUND(COUNT(A2.player_id) / COUNT(A1.player_id), 2)

As fraction FROM Activity A1 LEFT OUTER JOIN Activity A2 ON

A2.player_id = A1.player_id AND A2.event_date = A1.event_date + 1

WHERE (A1.player_id, A1.event_date) IN (SELECT player_id,

MIN(event_date) FROM Activity GROUP BY player_id )
SELECT b.fraction as fraction from (
SELECT
a.player_id,
a.device_id,
ROUND(COUNT(b.player_id) / (SELECT COUNT(DISTINCT a.player_id) FROM Activity), 2) AS fraction
FROM
Activity a
JOIN
Activity b ON a.event_date = DATE_ADD(b.event_date, INTERVAL 1 DAY)
WHERE
DATEDIFF(a.event_date, b.event_date) = 1
GROUP BY
a.player_id, a.device_id)b

leetcode 20: — — — — — — — — — — — —

left join is compulsory aur wo chahiye jo nahi hai t go for null entries.

select a.customer_id , count(*) as count_no_trans
from Visits a left join Transactions b
on a.visit_id = b.visit_id where b.amount is null group by a.customer_id

my solution was

SELECT customer_id, COUNT(visit_id) as count_no_trans 
FROM Visits v
WHERE NOT EXISTS (
SELECT visit_id FROM Transactions t
WHERE t.visit_id = v.visit_id
)
GROUP BY customer_id

--

--