#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter


CREATE TABLE IF NOT EXISTS `measurements` (
  `measurementID` int auto_increment NOT NULL,
  `weight` float NOT NULL,
  `height` float NOT NULL,
  PRIMARY KEY (`measurementID`));


/*
 1. Write code to identify & insert only unique rows from the datatable below into `measurements`.
  i.e. if a row appears to have a duplicate measurement (same height, same weight), do not
  insert that row.
  | weight | height |
  |--------|--------|
  |  45.1  | 165.3  |
  |  44.2  | 166.2  |
  |  45.1  | 162.1  |
  |  47.9  | 171.3  |
  |  44.2  | 166.2  |
  |  41.9  | 159.1  |
  */
  
CREATE TABLE IF NOT EXISTS `some_datatable` (
`weight` float NOT NULL,
`height` float NOT NULL);
-- write your insert statement here

/* my sample input */
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (45.1, 165.3);
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (44.2, 166.2);
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (45.1, 162.1);
 
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (47.9, 171.3);
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (44.2, 166.2);
INSERT INTO `some_datatable` (`weight`, `height`) VALUES (41.9, 159.1);
/* my sample input */ 


/* this willl cause an error as there is no some_datatable_somewhere_here in the sql but if there was an actual this would work*/
INSERT INTO measurements (weight, height)   
SELECT distinct dt.weight, dt.height FROM `some_datatable` as dt
where not exists(select 1 FROM `measurements` as m2 Where dt.weight = m2.weight And m2.height = dt.height); 


/* 2. Next, please write a select query that pulls the measurementID, weight, and height from the entry with the median weight */

/* BEGIN: helpers to get the median based on the number of rows, if odd or even */
SET @rank=0;
SET @midPoint=0;
SET @remainder = 0;
SET @median = -1.0;

SELECT @rownum,
       @rownum := @rownum + 1 AS rank
   FROM `measurements` as m, 
       (SELECT @rownum := 0) r
ORDER BY m.height;

SET @remainder = MOD(@rownum,2);  

SET @midPoint = @rownum / 2;
select @rownum, @midPoint, @remainder;

 
Select @median := CASE WHEN @remainder = 1 THEN t.height else sum(t.height) end as sumHt
FROM (
SELECT m.*, @rownum,
       @rownum := @rownum + 1 AS rank
   FROM `measurements` as m, 
       (SELECT @rownum := 0) r) t
WHERE ((t.rank = @midPoint OR t.rank = @midPoint + 1) AND @remainder = 0) OR
      ( Ceiling(@midPoint) = t.rank  AND @remainder != 0);
/* END: helpers to get the median based on the number of rows, if odd or even */      

/* THE ACTUAL SELECT THAT YOU ASKED FOR */
select  n.*, CASE
        WHEN @remainder = 0 THEN @median / 2
        ELSE @median
    END AS medianHeight
FROM `measurements` as n;
/* THE ACTUAL SELECT THAT YOU ASKED FOR */

-- please drop created tables
       
/* select @rownum, @midPoint, @remainder; */

DROP TABLE  `measurements` ;
drop table `some_datatable`;