Given the query below in green text, rewrite it to make it more readable and easy to maintain using your personal best practices in writing query. If you don't have any personal best practices, you may follow these simple rules to start with: Use inline comments if needed Use tabs instead of spaces Indent all sub-queries Use "WHERE 1=1" followed by " AND ..." Split column names to new lines Add empty lines for complex queries Do not shorten table name aliases Use the backtick (`) before and after table & column names Never use wildcard (*) operator in any query as much as possible, unless it's necessary Use uppercase letters to all sql keywords select e.id, concat(e.first_name, ' ', e.last_name) AS employee_full_name, e.first_name, d.id AS department_id, d.name AS last_department_name from employee e inner join ( select der.employee_id, max(der.id) AS max_id from department_employee_rel der where der.deleted_flag = 0 group by der.employee_id ) derm ON derm.employee_id = e.id inner join department_employee_rel der ON der.id = derm.max_id and der.deleted_flag = 0 inner join department d ON d.id = der.department_id and d.deleted_flag = 0 where e.id IN (10010, 10040, 10050, 91050, 205357) and e.deleted_flag = 0 limit 100;
Instructions
Given the query below in green text, rewrite it to make it more readable and easy to maintain using your personal best practices in writing query. If you don't have any personal best practices, you may follow these simple rules to start with:
Use inline comments if needed
Use tabs instead of spaces
Indent all sub-queries
Use "WHERE 1=1" followed by " AND ..."
Split column names to new lines
Add empty lines for complex queries
Do not shorten table name aliases
Use the backtick (`) before and after table & column names
Never use wildcard (*) operator in any query as much as possible, unless it's necessary
Use uppercase letters to all sql keywords
select e.id, concat(e.first_name, ' ', e.last_name) AS employee_full_name, e.first_name, d.id AS department_id, d.name AS last_department_name from employee e inner join ( select der.employee_id, max(der.id) AS max_id from department_employee_rel der where der.deleted_flag = 0 group by der.employee_id ) derm ON derm.employee_id = e.id inner join department_employee_rel der ON der.id = derm.max_id and der.deleted_flag = 0 inner join department d ON d.id = der.department_id and d.deleted_flag = 0 where e.id IN (10010, 10040, 10050, 91050, 205357) and e.deleted_flag = 0 limit 100;
Step by step
Solved in 2 steps
