Scribd
Upload
13 views3 pages

SQL Queries for Managing Swiggy Data

The document provides SQL queries for managing and analyzing data in the 'swiggy' table, including finding duplicate rows, deleting duplicates, and retrieving specific records. It also includes queries to find the latest orders, print order details with comments, calculate outlet-wise order counts and total bill amounts, and create a cross-tabulation of total bill amounts by customer and outlet. Overall, it serves as a guide for performing various data operations in a database context.

Uploaded by

aswathykaiprathu2003
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views3 pages

SQL Queries for Managing Swiggy Data

The document provides SQL queries for managing and analyzing data in the 'swiggy' table, including finding duplicate rows, deleting duplicates, and retrieving specific records. It also includes queries to find the latest orders, print order details with comments, calculate outlet-wise order counts and total bill amounts, and create a cross-tabulation of total bill amounts by customer and outlet. Overall, it serves as a guide for performing various data operations in a database context.

Uploaded by

aswathykaiprathu2003
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Introtallent.

com
# Q: find count of duplicate rows in the swiggy table*/

Select id,count(id) from [Link]

Group by id

Having count(id)>1

Order by count(id) desc;

#Q: /*-----------------Remove Duplicate records --------------------*/

/*How would you delete duplicate records from the table*/

/*Step1

create a new table by taking uniques record from original table*/

Create table project.swiggy1

As

Select Distinct * from [Link];

/*Step 2:

Delete the original table*/

DROP table [Link];

/*Step3:

Rename the new table to original table name*/

Rename table project.swiggy1 to [Link];

# Q: print records from row number 4 to 9

Select * from [Link]

limit 3,6;
[Link]
# Q: Find the latest order placed by customers.

with latest_order as

(select cust_id, outlet, order_date,

row_number() over(partition by cust_id order by order_date desc) as latest_ord_dt

from [Link]) select cust_id, outlet, order_date from latest_order where latest_ord_dt = 1;

# Q: Print order_id, partner_code, order_date, comment (No issues in place of null else comment)

Select order_id, partner_code,order_date,

(case

when comments is null then 'No issues'

else comments end) as comments

from [Link];

# Q: Print outlet wise order count, cumulative order count, total bill_amount, cumulative
bill_amount

Select [Link], a. order_cnt, @running_ord_count:=@running_ord_count + a.order_cnt AS


cumulative_cnt,

a.total_sale, @running_sale:=@running_sale + a.total_sale as cum_sale

from

(Select outlet, count(order_id) as order_cnt, sum(bill_amount) as total_sale from [Link]


group by outlet) a

Join (Select @running_ord_count:=0, @running_sale:=0) b

order by outlet;

# Q: Print cust_id wise, Outlet wise 'total number of orders'

Select cust_id,

sum(if(outlet='KFC',1,0)) KFC,

sum(if(outlet='Dominos',1,0)) Dominos,

sum(if(outlet='Pizza hut',1,0)) Pizza_hut

from [Link]

group by 1;
[Link]

# Q: Create a cross tab cust_id wise outlet wise total bill amount

Select cust_id,

sum(if(outlet='KFC',bill_amount,0)) KFC,

sum(if(outlet='Dominos',bill_amount,0)) Dominos,

sum(if(outlet='Pizza hut',bill_amount,0)) Pizza_hut

from [Link]

group by 1;

Select * from [Link];

You might also like