## Cameron91

BAN USERI am a kid.

sivabasava, To handle data which is dynamically added at runtime. I modified the algorithm of the esteemed investment banking firm Goldman Sachs engineers --David Rozenshtein, Anatoly Abramovich , Eugene Birger in the following way

CREATE INDEX IDX_HOURS ON BULBLIFE(HOURS ASC)

Each time I insert data, INSERT INTO BULBLIFE VALUES(5 or 7). the SQLITE INDEX data structure gets modified

As a result when the Goldman Sachs' engineers' SQL statement is applied after each insert statement., the new median is accurately determined and changes if required. Please let me know if my modification is correct. Thank you Cameron Chang.

sivabasava, please let me know your professional opinion of the following SQLITE MEDIAN function. Thank you Cameron91

{{

CREATE TABLE BULBLIFE(HOURS INT)

INSERT INTO BULBLIFE VALUES(4)

INSERT INTO BULBLIFE VALUES(4)

INSERT INTO BULBLIFE VALUES(4)

INSERT INTO BULBLIFE VALUES(4)

INSERT INTO BULBLIFE VALUES(6)

INSERT INTO BULBLIFE VALUES(6)

INSERT INTO BULBLIFE VALUES(6)

INSERT INTO BULBLIFE VALUES(6)

Following is a SQLITE MEDIAN function solution credited to David Rozenshtein, Anatoly Abramovich, and Eugene Birger

SELECT x.hours median

FROM BULBLIFE x, BULBLIFE y

GROUP BY x.HOURS

HAVING

SUM(CASE WHEN y.HOURS <= x.HOURS

THEN 1 ELSE 0 END) >= (COUNT(*) + 1)/2 AND

SUM(CASE WHEN y.HOURS >= x.HOURS

THEN 1 ELSE 0 END) >=(COUNT(*) + 1)/2 + 1

SQLITE returns MEAN 4

}}

According to wikipedia, if you have an even number of observations ,the median has several alternative definitions the choice being either 1) always the smallest one 2) always the largest one 3) randomly choose between the two. The advantages of 1) , 2) and 3) are that it is guaranteed that the the median is always a list element(e.g a list of integers will never have a fractional median) and it guarantees that the median exists for any ordinal valued data. sivabasava, how would you adapt your program to the alternative definitions of median?

- Cameron91 October 07, 2012

Rep**paytonmorrison48**, Data Scientist at ADPI am working as a date entry keyar . I have many years of experience . The data entry manager is primarily ...

**CareerCup**is the world's biggest and best source for software engineering interview preparation. See all our resources.

Open Chat in New Window

- Cameron91 October 07, 2012