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 [...]

Group rows by the specified order

To 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 [...]

Sort by specified order

To 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 [...]

Compare over the previous period for consecutive intervals

To find out the longest run of consecutive days of gains 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 complex order [...]

Compare over the previous period for selected records

To find out the three trading days with the highest stock price, as well as the closing price and increasing of that stock on these three days. 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) [...]

Filter accumulated value

To find out the customers who account for half of the total sales and sort them from the highest to the lowest by their 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 [...]

Select subset according to the position

To 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) [...]


To find out the employee whose age ranks the middle 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 number for rows before they can use the conditional [...]

Find records with the maximum and the minimum

To find out the interval of joined dates between the oldest employee and 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 function to get the max/min value cannot retrieve the record directly. To get the record, [...]

Union tables with different structure

To 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 structure. 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 [...]