WITH target AS (
	SELECT
	    TOTAL_VALUE
	  , count(*) over() as c
	FROM
	    CONVENIENCE
	WHERE
	    SURVEY_YEAR = 2019
	AND KIND_CODE   = '100'
)
, odd AS (
    SELECT DISTINCT
        c
      , nth_value(TOTAL_VALUE, (c+1)/2)    OVER(ORDER BY TOTAL_VALUE DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)   AS SA_MEDIAN
    FROM
        target
)
, even AS (
    SELECT DISTINCT
        c
      , round(
          (
             nth_value(TOTAL_VALUE, c/2  ) OVER(ORDER BY TOTAL_VALUE DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
           + nth_value(TOTAL_VALUE, c/2+1) OVER(ORDER BY TOTAL_VALUE DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
           ) * 1.0
          / 2
        ) AS SA_MEDIAN
    FROM
        target
)
SELECT
    iif(odd.c % 2 = 1, odd.SA_MEDIAN, even.SA_MEDIAN) AS SA_MEDIAN
FROM
    odd, even
;