Overlapped conditional groups

To group the employees by their length of service: less than one year, one to three years, more than three years, and more than five years, and compute the number of male and female employees respectively in each group. WITH A AS (SELECT 1 sequence_no.,’less_than_a_year’ division, 0 lower_bound,1 upper_bound FROM dual UNION SELECT 2,’one_to_three_years’,1,3 FROM [...]

Grouping rows by the specified order

List the number of female employees and their average age in each of the following departments: R&D, Sales, Marketing, and Administration. WITH A AS (SELECT 1 seq_no., ‘R&D’ dept FROM dual UNION SELECT 2, ‘sales’ FROM dual UNION SELECT 3, ‘marketing’ FROM dual UNION SELECT 4, ‘admin’ FROM dual ) SELECT A.dept,count(*) employee_total,avg(B.age) average_age FROM [...]

Sorting by specified order

Compute the increase in sales of the first month of a quarter compared to the first month of the previous quarter. There may be no data available for some months. WITH M AS (SELECT 1 month FROM dual UNION SELECT 4 FROM dual UNION SELECT 7 FROM dual UNION SELECT 10 FROM dual) SELECT month,sales_amount, [...]

Getting consecutive intervals by comparing to the previous day

Find out the longest run of consecutive days of gains in the price for a stock. SELECT max(consecutive_day) FROM (SELECT count(*) consecutive_day FROM (SELECT sum(rise_or_fall) OVER(ORDER BY trade_date) day_no_gain FROM (SELECT trade_date, CASE when close_price>lag(close_price) OVER(ORDER BY trade_date) then 0 else 1 end rise_or_fall FROM stock_price) ) GROUP BY day_no_gain) SQL syntax cannot handle the [...]

Calculating YOY growth rates on selected records

Find out the three trading days with the highest stock prices, as well as their closing prices and growth rates. SELECT * FROM (SELECT trade_date,close_price, close_price/lag(close_price) OVER(ORDER BY trade_date)-1 rising_range FROM stock_price) WHERE trade_date IN (SELECT trade_date FROM (SELECT trade_date, row_number() OVER(ORDER BY close_price desc) stock_rank FROM stock_price) WHERE stock_rank<=3) The users can use window [...]

Filtering accumulated values

Find out the customers who account for half of the total sales and sort them from the highest to the lowest by the sales amount. WITH A AS (SELECT customer,sales_amount, row_number() OVER (ORDER BY sales_amount) sales_rank FROM customer_sales) SELECT customer,sales_amount FROM (SELECT customer,sales_amount, sum(sales_amount) OVER (ORDER BY sales_rank) accumulated_amount FROM A) WHERE accumulated_amount>(SELECT sum(sales_amount)/2 FROM [...]

Selecting subset according to the position

Divide the students into two classes according to their scores by using the snake algorithm, that is, Class 1 has students who ranks 1, 4, 5, 8 … Class 2 has those who ranks 2, 3, 6, 7 … .        Class 1: SELECT name,score FROM (SELECT name,score, row_number() OVER(ORDER BY score desc) score_rank FROM score_table) [...]


Find out the employee whose age is the median of the ages of all employees. SELECT name,birthday FROM (SELECT name,birthday, row_number() OVER (ORDER BY birthday) age_rank FROM employee) WHERE age_rank=(SELECT floor((count(*)+1)/2) FROM employee) The SQL record set is unordered, so the user will need to create the sequence numbers for rows before they can use [...]

Find records with the maximum and the minimum

Find out the interval (days) between the joining date of the oldest employee and the joining date of the youngest employee. SELECT (SELECT join_date FROM employee WHERE birthday=(SELECT min(birthday) FROM employee)) – (SELECT join_date FROM employee WHERE birthday=(SELECT max(birthday) FROM employee)) FROM dual The SQL max/min functions cannot retrieve records directly. To get the desired [...]

Combining tables with different structures

Find out the average age of female employees (including both regular and temporary employees). The regular_employee and temp_employee tables may have different data structures. SELECT avg(age) FROM (SELECT age,gender FROM regular_employee UNION ALL SELECT age,gender FROM temp_employee) WHERE gender=’female’ The SQL union all operator cannot union tables of different structures. The user will need to [...]