Write an SQL query to list customers who have not placed an order in last 6 months but have placed more than 5 orders in total
SELECT a.customer_id ,b.name
FROM Orders101 AS a
INNER JOIN Customers101 AS b
ON a.Customer_id = b.Customer_id
GROUP BY customer_id
HAVING MONTH(CURRENT_DATE())-MONTH(max(a.Order_Date)) > 6
AND count(*) > 5;
Write an SQL query to Fetch month on month return rate
SELECT MONTH(order_date) AS Month,
CONCAT(ROUND( ( SUM(CASE
WHEN order_status ='returned' THEN 1
ELSE 0
END )/ count(DISTINCT Order_id) ) *100 , 2) , '%') AS 'return_rate'
FROM order_fact
GROUP BY MONTH(order_date);
Calculate consecutive order counts
WITH cte AS (
SELECT Seq, Order_type,
SUM(CASE WHEN Order_type = prev_Order_type THEN 0 ELSE 1 END) OVER(ORDER BY Seq) AS grp
FROM (SELECT *, LAG(Order_type) OVER(ORDER BY Seq) AS prev_Order_type FROM Order_batch) s
)
SELECT ROW_NUMBER() OVER() AS Seq, Order_type AS 'Order' ,count(*) AS 'Count'
FROM cte
GROUP BY Order_type, grp;
Write a SQL query to find the position of alphabet ('a') int the first name column 'Shivansh' from Student table.
SELECT INSTR(FIRST_NAME, 'a') FROM Student WHERE FIRST_NAME = 'Shivansh';
Write an SQL query to print details of the Students whose FIRST_NAME ends with ‘a’ and contains five alphabets.
SELECT * FROM Student WHERE FIRST_NAME LIKE '_____a';
Write a query to find the top 5 customers with the highest total order amounts.
select top 5(customer_id) from (select customer_id, sum(order_amount) total_amt from orders group by customer_id order by total_amt desc);
SELECT CustomerID, SUM(OrderAmount) AS TotalOrderAmount FROM Orders GROUP BY CustomerID ORDER BY TotalOrderAmount DESC LIMIT 5;
Remove duplicate values from employee table
select distinct * from employee;
Find out duplicate values from employee table
with cte as (
select *, row_number() over (partition by employee_id order by employee_id) as rn from employee;
)
select * from cte where rn = 2;
Find out highest earning employee based on each position
select position, max(salary) from employee group by position;
Get top 3 highest/lowest earning employees
select top 3 * from employee order by salary desc/asc; (OR)
with cte as(
select distinct * from employee
)
select top 3 * from cte order by by salary desc/asc; // there are duplicate rows
Get 3rd highest/loweest salary from employee table
with cte as(
select *, row_number() over (order by salary desc/asc) as rn from employee; // duplicate salaries
)
select emp_name, emp_sal from employee where rn = 3;
Get 3rd highest salary based on each department
with cte as(
select *, row_number() over (partition by department order by salary desc) as rn from employees)
select department, salary from cte where rn = 3;
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
