w3resource

SQL Challenges-1: Execution of orders: Overall execution Rate

SQL Challenges-1: Exercise-19 with Solution

From the following tables, write a SQL query to find the overall rate of execution of orders, which is the number of orders execution divided by the number of orders quote. Return rate_of_execution rounded to 2 decimals places.

Input:

Table: orders_issued

Structure:

FieldTypeNullKeyDefaultExtra
distributor_idint(11)YES
company_idint(11)YES
quotation_datedateYES

Data:

distributor_idcompany_idquotation_date
1012022019-11-15
1012032019-11-15
1012042019-11-15
1022022019-11-16
1022012019-11-15
1032032019-11-17
1032022019-11-17
104 2032019-11-18
1042042019-11-18

Table: orders_executed

Structure:

FieldTypeNullKeyDefaultExtra
orders_fromint(11)YES
executed_fromint(11)YES
executed_datedateYES

Data:

orders_fromexecuted_fromexecuted_date
1012022019-11-17
1012032019-11-17
1022022019-11-17
1032032019-11-18
1032022019-11-19
104 2032019-11-20

Sample Solution:

SQL Code(MySQL):

CREATE TABLE orders_issued (distributor_id int, company_id int, quotation_date date);
INSERT INTO orders_issued VALUES (101, 202, '2019-11-15');
INSERT INTO orders_issued VALUES (101, 203, '2019-11-15');
INSERT INTO orders_issued VALUES (101, 204, '2019-11-15');
INSERT INTO orders_issued VALUES (102, 202, '2019-11-16');
INSERT INTO orders_issued VALUES (102, 201, '2019-11-15');
INSERT INTO orders_issued VALUES (103, 203, '2019-11-17');
INSERT INTO orders_issued VALUES (103, 202, '2019-11-17');
INSERT INTO orders_issued VALUES (104, 203, '2019-11-18');
INSERT INTO orders_issued VALUES (104, 204, '2019-11-18');

CREATE TABLE orders_executed (orders_from int, executed_from int, executed_date date);
INSERT INTO orders_executed VALUES (101, 202, '2019-11-17');
INSERT INTO orders_executed VALUES (101, 203, '2019-11-17');
INSERT INTO orders_executed VALUES (102, 202, '2019-11-17');
INSERT INTO orders_executed VALUES (103, 203, '2019-11-18');
INSERT INTO orders_executed VALUES (103, 202, '2019-11-19');
INSERT INTO orders_executed VALUES (104, 203, '2019-11-20');

SELECT
ROUND(
    IFNULL(
    (SELECT COUNT(*) FROM (SELECT DISTINCT orders_from, executed_from FROM orders_executed) AS A)
    /
    (SELECT COUNT(*) FROM (SELECT DISTINCT distributor_id, company_id FROM orders_issued) AS B),
    0)
, 2) AS rate_of_execution;

Sample Output:

rate_of_execution|
-----------------|
             0.67|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Orders items 5 or more times.
Next: Audience in the match.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8