Zomato Dataset SQL Project - KPIs & Queries
Key KPIs and Their Relevance
1. Top-rated restaurants - Useful for promotions and featured listings.
2. Revenue estimation (Votes Cost) - Proxy for restaurant performance.
3. Most active cities - Helps prioritize expansion and delivery zones.
4. Most popular cuisines - Useful for launching new outlets or food types.
5. Avg. cost per city - Helps assess affordability across regions.
6. City-level performance (rating, cost, revenue) - Regional benchmarking.
SQL Queries with Relevance (30 Questions)
-- 1. List all unique cities present in the dataset
-- Helps understand market spread and geographic coverage
SELECT DISTINCT City FROM zomato_dataset;
-- 2. Count the total number of restaurants in the dataset
-- Gives a basic understanding of dataset volume
SELECT COUNT(*) AS Total_Restaurants FROM zomato_dataset;
-- 3. Find the top 5 restaurants with the highest rating
-- Identifies top-performing restaurants for possible promotions
SELECT RestaurantName, Rating
FROM zomato_dataset
ORDER BY Rating DESC
LIMIT 5;
-- 4. Get the average cost for two in each city
-- Understands the pricing trend and affordability by region
SELECT City, AVG(Average_Cost_for_two) AS Avg_Cost
FROM zomato_dataset
GROUP BY City;
-- 5. Find all restaurants that offer online delivery
-- Useful for operational logistics and platform service analysis
SELECT *
FROM zomato_dataset
WHERE Has_Online_delivery = 'Yes';
-- 6. Get a list of distinct cuisines available
-- Helps analyze food diversity and customer options
Zomato Dataset SQL Project - KPIs & Queries
SELECT DISTINCT Cuisines FROM zomato_dataset;
-- 7. Count restaurants by price range
-- Used for customer segmentation and pricing model insights
SELECT Price_range, COUNT(*) AS Count
FROM zomato_dataset
GROUP BY Price_range;
-- 8. Show the total votes received by all restaurants
-- Indicates total user engagement and review volume
SELECT SUM(Votes) AS Total_Votes FROM zomato_dataset;
-- 9. Find restaurants in 'Pasay City' with ratings above 4.5
-- Helps identify premium or highly-rated restaurants in a specific area
SELECT RestaurantName, Rating
FROM zomato_dataset
WHERE City = 'Pasay City' AND Rating > 4.5;
-- 10. List restaurants with null or empty Cuisines
-- Important for data cleaning and handling missing values
SELECT *
FROM zomato_dataset
WHERE Cuisines IS NULL OR Cuisines = '';
-- 11. Use CASE to categorize restaurants based on rating
-- Converts numerical rating into qualitative labels for easier reporting
SELECT RestaurantName, Rating,
CASE
WHEN Rating >= 4.5 THEN 'Excellent'
WHEN Rating >= 3.5 THEN 'Good'
WHEN Rating >= 2.5 THEN 'Average'
ELSE 'Poor'
END AS Rating_Category
FROM zomato_dataset;
-- 12. Find the city with the highest average rating
-- Helps identify best-performing cities by customer satisfaction
SELECT City, AVG(Rating) AS Avg_Rating
FROM zomato_dataset
GROUP BY City
ORDER BY Avg_Rating DESC
LIMIT 1;
Zomato Dataset SQL Project - KPIs & Queries
-- 13. Show restaurants with cost above their citys average
-- Identifies premium restaurants for pricing and business strategy
SELECT *
FROM zomato_dataset z
WHERE Average_Cost_for_two > (
SELECT AVG(Average_Cost_for_two)
FROM zomato_dataset
WHERE City = [Link]
);
-- 14. Get count of restaurants per cuisine per city
-- Analyzes cuisine preference patterns in different locations
SELECT City, Cuisines, COUNT(*) AS Restaurant_Count
FROM zomato_dataset
GROUP BY City, Cuisines;
-- 15. Find the restaurant with the maximum votes in each city
-- Shows most popular or best-reviewed restaurant in each city
SELECT City, RestaurantName, Votes
FROM (
SELECT *, RANK() OVER(PARTITION BY City ORDER BY Votes DESC) AS rnk
FROM zomato_dataset
) ranked
WHERE rnk = 1;
-- 16. Calculate running total of votes by city
-- Demonstrates cumulative user engagement using window functions
SELECT City, RestaurantName, Votes,
SUM(Votes) OVER (PARTITION BY City ORDER BY Votes DESC) AS Running_Total
FROM zomato_dataset;
-- 17. Get total revenue generated per city (Votes Cost)
-- Revenue proxy KPI for financial performance by location
SELECT City, SUM(Votes * Average_Cost_for_two) AS Total_Revenue
FROM zomato_dataset
GROUP BY City;
-- 18. List restaurants that have table booking but no online delivery
-- Identifies traditional dining experiences with modernization potential
SELECT *
FROM zomato_dataset
Zomato Dataset SQL Project - KPIs & Queries
WHERE Has_Table_booking = 'Yes' AND Has_Online_delivery = 'No';
-- 19. Find duplicate entries of RestaurantID
-- Detects and prevents data integrity issues
SELECT RestaurantID, COUNT(*) AS Count
FROM zomato_dataset
GROUP BY RestaurantID
HAVING COUNT(*) > 1;
-- 20. Create a view showing restaurants with rating > 4
-- Helps create reusable logic for dashboards and analytics
CREATE OR REPLACE VIEW HighRatedRestaurants AS
SELECT *
FROM zomato_dataset
WHERE Rating > 4;
-- 21. Rank restaurants by revenue within each city
-- Enables leaderboard creation based on performance
SELECT City, RestaurantName, Votes * Average_Cost_for_two AS Revenue,
RANK() OVER(PARTITION BY City ORDER BY Votes * Average_Cost_for_two DESC) AS Revenue_Rank
FROM zomato_dataset;
-- 22. Use subquery to find restaurants above global average cost
-- Identifies premium or overpriced restaurants for pricing insights
SELECT *
FROM zomato_dataset
WHERE Average_Cost_for_two > (
SELECT AVG(Average_Cost_for_two) FROM zomato_dataset
);
-- 23. Find top 3 cuisines by total votes
-- Reveals most popular food types based on customer interest
SELECT Cuisines, SUM(Votes) AS Total_Votes
FROM zomato_dataset
GROUP BY Cuisines
ORDER BY Total_Votes DESC
LIMIT 3;
-- 24. Delete duplicate rows while keeping the one with highest RestaurantID
-- Cleans up the data by removing redundant entries
DELETE z1 FROM zomato_dataset z1
JOIN zomato_dataset z2
Zomato Dataset SQL Project - KPIs & Queries
ON [Link] = [Link] AND [Link] = [Link]
WHERE [Link] < [Link];
-- 25. Add a new column 'Revenue' and update it (Votes Cost)
-- Derives a business-focused metric using calculated fields
ALTER TABLE zomato_dataset ADD Revenue FLOAT;
UPDATE zomato_dataset SET Revenue = Votes * Average_Cost_for_two;
-- 26. Identify the most expensive city by average cost
-- Helps locate luxury dining hubs or expensive markets
SELECT City, AVG(Average_Cost_for_two) AS Avg_Cost
FROM zomato_dataset
GROUP BY City
ORDER BY Avg_Cost DESC
LIMIT 1;
-- 27. Find top-rated restaurant per cuisine
-- Determines the best offering for each cuisine type
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Cuisines ORDER BY Rating DESC) AS rn
FROM zomato_dataset
) ranked
WHERE rn = 1;
-- 28. Count restaurants with votes above average
-- Filters engaged restaurants from the rest
SELECT COUNT(*) AS Count_Above_Avg_Votes
FROM zomato_dataset
WHERE Votes > (SELECT AVG(Votes) FROM zomato_dataset);
-- 29. Find max/min cost restaurants per city using window function
-- Allows full spectrum cost analysis for city-level strategy
SELECT City, RestaurantName, Average_Cost_for_two,
RANK() OVER(PARTITION BY City ORDER BY Average_Cost_for_two DESC) AS MaxRank,
RANK() OVER(PARTITION BY City ORDER BY Average_Cost_for_two ASC) AS MinRank
FROM zomato_dataset;
-- 30. Create a summary view for city-wise reporting
-- Final executive dashboard: overall stats per city
CREATE OR REPLACE VIEW CitySummary AS
SELECT City,
COUNT(*) AS TotalRestaurants,
Zomato Dataset SQL Project - KPIs & Queries
AVG(Rating) AS AvgRating,
SUM(Votes) AS TotalVotes,
SUM(Average_Cost_for_two) AS TotalCost
FROM zomato_dataset
GROUP BY City;