Doing self join with same table with range condition. The below is sql statement. select current_rec .* ,compare_rec.employee_id as less_salary_employee from hr.employees current_rec left join hr.employees compare_rec on current_rec.hire_date < compare_rec .hire_date and current_rec.salary < compare_rec.salary order by current_rec .employee_id…
I have a query which first performs joins inside cte and then based on cte join with other table and perform group by with some aggregations and get the top 15 records. WITH join_table as ( SELECT pl.yearmonth, pl.pdid, pl.OrderId,…
I have the following two tables: CREATE TABLE `ip_info` ( `start_ip` int(10) unsigned NOT NULL, `end_ip` int(10) unsigned NOT NULL, `country_code` varchar(3) DEFAULT NULL, `country_name` varchar(255) DEFAULT NULL, `continent_code` varchar(3) DEFAULT NULL, `continent_name` varchar(255) DEFAULT NULL, `asn` int(10) unsigned DEFAULT…
Column A Column B Column C Column D A B C A D A A B C A D A This is the table for instance. We are supposed to find the number of occurrences of A in the entire…
I have the following SQL statement: SELECT DISTINCT e.eventid, e.objectid, e.clock, e.ns, e.name, e.severity FROM events e, functions f, items i, hosts_groups hg WHERE e.source = '0' AND e.object = '0' AND NOT EXISTS (SELECT NULL FROM functions f, items…
From the following table of sport matches: id_ p1_id p2_id match_date p1_stat p2_stat 852666 1 2 01/01/1997 1301 249 852842 1 2 13/01/1997 2837 2441 853471 2 1 05/05/1997 1474 952 4760 2 1 25/05/1998 1190 1486 6713 2 1…
I have the following query: SELECT * FROM customers_cards WHERE id NOT IN ( SELECT card_id FROM customers_card_psp) It takes about 2sec to get executed and we need this query to get called on a highly visited page. Any idea…
I have a very simple mysql query join for storing records of instagram profiles and their posts. profiles table = 2500 records (PK = p.id) post_files table = 800,000 records (PK = pf.id) SELECT p.id, p.instagram_id, pf.filename, p.last_updated_on, count(pf.id) as…
I have the following query: SELECT m.id_ AS match_id, mv.id_ AS match_version_id, t.id_ AS tournament_id FROM test.match_version AS mv JOIN test.match_ AS m ON m.id_ = mv.match_id JOIN test.tournament AS t ON t.orig_id = m.tournament_orig_id AND t.tour_id = m.tour_id ORDER…
This query below takes way too long to execute select s.id, s.description, count(distinct et.id) from "district s" s inner join ( select id.id from "industry_districts" id ) as id2 on id2.id = s.id inner join ( select id, industry_districts_id from…