MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE cas              -- French for "case" - case is an SQL keyword and should
(                             -- not be used for table names!
  case_id INTEGER NOT NULL,
  cat     TEXT    NOT NULL,
  birth   DATE    NOT NULL,   -- store as a date - only requires 4 bytes
  c_type  TEXT    NULL        -- type is a keyword, best avoided 
);                            -- https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words
INSERT INTO cas (case_id, cat, birth) VALUES

(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE'      , '1995-08-22'),

(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD'     , '2005-03-19'),
(20033831, 'CHILD'     , '2006-03-25'),
(20033831, 'CHILD'     , '2010-05-20'),
(20033831, 'CHILD'     , '2013-10-25'),

(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE'      , '2001-07-05'),
(20039301, 'CHILD'     , '2018-10-22'),


(20039334, 'CASE_OWNER', '1994-03-10'),

(30033333, 'CASE_OWNER', '1980-01-01'),  -- added a single case owner with one child!
(30033333, 'CHILD'     , '2012-09-01'),

(30044444, 'CASE_OWNER', '2015-08-10'),  -- added a case owner < 21 yrs of age!

(30055555, 'CASE_OWNER', '1970-02-10'),  -- added a couple whose children are all > 21!
(30055555, 'WIFE'      , '1972-07-05'),
(30055555, 'CHILD'     , '1995-11-22'),
(30055555, 'CHILD'     , '1997-05-19'),


(30066666, 'CASE_OWNER', '1970-02-10'),  -- added single case owner whose children
(30066666, 'CHILD'     , '1989-07-05'),  -- are all over 21!
(30066666, 'CHILD'     , '1992-11-22'),
(30066666, 'CHILD'     , '1994-05-19');


--
-- To check
--

-- SELECT * FROM cas;
--
--
-- 1st query - works only with versions of MySQL >= 8 - uses window functions!
--
--


SELECT
  case_id,
  cat,
  birth,
  c_type,
  CASE
  
-- 1st section:
  
--  
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than 
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
  
  
    WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1 
      THEN 
        CASE
          WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
            THEN 'SINGLE_PERSON'
          ELSE 'UNKNOWN'
        END
    
    
-- 2nd section:
  
-- 
-- The logic is that  if the COUNT of case_id is = 2 **and** there's a 'WIFE', then
-- it must be a couple with no children - 'PAIR_NO_CHILD'.
--

    WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2 
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN 'PAIR_NO_CHILD'
    

-- 3rd section:

--
-- Here we look at where the COUNT of 'case_id's is >= 3. There may or may not
-- be children - so we again check for the exisence of a wife and if there is
-- one, then we check the age of the youngest child (MIN...) - the requirements
-- state that if even 1 child is < 21, then the case_id is classified as 
-- 'PAIR_WITH_CHILD' and if no child is under 21, then it's 'OTHER'
--

    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END  

    
-- 4th section:

-- 
-- If the COUNT of 'case_id' is > 1 (or >= 2...) AND there's no wife, then the
-- others with that 'case_id' **must** be children. Then, with the 'CASE', we
-- determine the age of the youngest child. According to the requirements, having
-- one child < 21, means that all children are counted (and the type is 
-- 'SINGLE_WITH_CHILD' - but if **all** of them are <= 21, then the type is 'MULTIPLE'
--  

    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END 
  
    ELSE 'No assigned type'
    
    
  END AS c_t

  -- 5th section: 

FROM
  cas
ORDER BY 
  case_id,
  CASE
    WHEN cat = 'CASE_OWNER' THEN 1
    WHEN cat = 'WIFE'       THEN 2
    WHEN cat = 'CHILD'      THEN 3
  END,
  birth DESC;


case_id
cat
birth
c_type
c_t


20033738
CASE_OWNER
1996-04-08
null
PAIR_NO_CHILD

20033738
WIFE
1995-08-22
null
PAIR_NO_CHILD

20033831
CASE_OWNER
1975-03-05
null
SINGLE_WITH_CHILD

20033831
CHILD
2013-10-25
null
SINGLE_WITH_CHILD

20033831
CHILD
2010-05-20
null
SINGLE_WITH_CHILD

20033831
CHILD
2006-03-25
null
SINGLE_WITH_CHILD

20033831
CHILD
2005-03-19
null
SINGLE_WITH_CHILD

20039301
CASE_OWNER
1999-07-27
null
PAIR_WITH_CHILD

20039301
WIFE
2001-07-05
null
PAIR_WITH_CHILD

20039301
CHILD
2018-10-22
null
PAIR_WITH_CHILD

20039334
CASE_OWNER
1994-03-10
null
SINGLE_PERSON

30033333
CASE_OWNER
1980-01-01
null
SINGLE_WITH_CHILD

30033333
CHILD
2012-09-01
null
SINGLE_WITH_CHILD

30044444
CASE_OWNER
2015-08-10
null
UNKNOWN

30055555
CASE_OWNER
1970-02-10
null
OTHER

30055555
WIFE
1972-07-05
null
OTHER

30055555
CHILD
1997-05-19
null
OTHER

30055555
CHILD
1995-11-22
null
OTHER

30066666
CASE_OWNER
1970-02-10
null
MULTIPLE

30066666
CHILD
1994-05-19
null
MULTIPLE

30066666
CHILD
1992-11-22
null
MULTIPLE

30066666
CHILD
1989-07-05
null
MULTIPLE


--
--
-- 2nd query - work with versions of MySQL >= 5.5 - this is the earliest version
-- for which a server is avaiable on db<>fiddle. 5.5 entered EoL 3 years ago!
--
-- Try changing the version of MySQL using the dropdown at the top of this
-- fiddle!
--


SELECT
  s.case_id,
  s.cat,
  s.birth,

--
-- 1st section: these sections correspond to the sections in query 1 above. 
--

  CASE
    WHEN s.c_cnt = 1
      THEN
        CASE
          WHEN s.a_min >= 21
            THEN 'SINGLE PERSON'
          ELSE 'UNKNOWN'
        END
  
        
--
-- 2nd section:
--
        
    WHEN s.c_cnt = 2  AND s.w_cnt = 1
      THEN 'PAIR_NO_CHILD'
  
        
--  
-- 3rd section:
--
        
    WHEN s.c_cnt >= 3 AND s.w_cnt = 1
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END          
  
  
--
-- 4th section:
--
        
    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 21 
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END                 
        
        
    ELSE 'No assigned type!'

  END AS c_t
  
FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
    
    (SELECT FLOOR(MIN(DATEDIFF(NOW(), c4.birth) / 365.25)) 
       FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min    
       
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s;


case_id
cat
birth
c_t


20033738
CASE_OWNER
1996-04-08
PAIR_NO_CHILD

20033738
WIFE
1995-08-22
PAIR_NO_CHILD

20033831
CASE_OWNER
1975-03-05
SINGLE_WITH_CHILD

20033831
CHILD
2013-10-25
SINGLE_WITH_CHILD

20033831
CHILD
2010-05-20
SINGLE_WITH_CHILD

20033831
CHILD
2006-03-25
SINGLE_WITH_CHILD

20033831
CHILD
2005-03-19
SINGLE_WITH_CHILD

20039301
CASE_OWNER
1999-07-27
PAIR_WITH_CHILD

20039301
WIFE
2001-07-05
PAIR_WITH_CHILD

20039301
CHILD
2018-10-22
PAIR_WITH_CHILD

20039334
CASE_OWNER
1994-03-10
SINGLE PERSON

30033333
CASE_OWNER
1980-01-01
SINGLE_WITH_CHILD

30033333
CHILD
2012-09-01
SINGLE_WITH_CHILD

30044444
CASE_OWNER
2015-08-10
UNKNOWN

30055555
CASE_OWNER
1970-02-10
OTHER

30055555
WIFE
1972-07-05
OTHER

30055555
CHILD
1997-05-19
OTHER

30055555
CHILD
1995-11-22
OTHER

30066666
CASE_OWNER
1970-02-10
MULTIPLE

30066666
CHILD
1994-05-19
MULTIPLE

30066666
CHILD
1992-11-22
MULTIPLE

30066666
CHILD
1989-07-05
MULTIPLE


--
-- MySQL Performance analysis - feel free to ignore...
--
-- see the brief discussion at the end of the answer!
--
-- The question is here: https://dba.stackexchange.com/questions/305706/mysql-update-column-based-on-multiple-conditions
--

--
-- In order to give every chance to the slower query, I'm performing the windows
-- function query first - any caching should be in favour of the second query, the one
-- which uses a subquery and which works on MySQL >= 5.5
--
--
-- What, exactly, is happening here escapes me! I'm not going to investigate further
-- because (much wasted) time and experience has shown me that MySQL's bugs/
-- performance issues/whatever are not worth pursuing, because it's either
-- documented in an obscure part of the manual or it's a recently introduced
-- bug which has silently been fixed in a few versions down the road or it's 
-- always going to be this way.
--

-- 
-- Suffice to say that the windows function query is ~ 5 - 10 times more performant
-- than the subquery variant - this is because the subquery has to scan the 
-- base cas table at least 4 times. That's once for the initial read and then 
-- 3 more times for the aggregate functions - COUNT(), SUM() and MIN().

--
-- Tests on my own machine show that this is the case with MySQL Server running
-- locally
-- 

EXPLAIN ANALYZE   -- EXPLAIN ANALYZE only works on MySQL >= 8.0.18
                  -- https://dev.mysql.com/blog-archive/mysql-explain-analyze/
SELECT
  case_id,
  cat,
  birth,
  c_type,
  CASE
  
-- 1st section:
  

  
    WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1 
      THEN 
        CASE
          WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
            THEN 'SINGLE_PERSON'
          ELSE 'UNKNOWN'
        END
    
    
-- 2nd section:
  

    WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2 
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN 'PAIR_NO_CHILD'
    

-- 3rd section:


    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END  

    
-- 4th section:


    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END 
  
    ELSE 'No assigned type'
    
    
  END AS c_t

  -- 5th section: 

FROM
  cas
ORDER BY 
  case_id,
  CASE
    WHEN cat = 'CASE_OWNER' THEN 1
    WHEN cat = 'WIFE'       THEN 2
    WHEN cat = 'CHILD'      THEN 3
  END,
  birth DESC;
 
  
  


EXPLAIN


-> Sort: cas.case_id, `(case when (cas.cat = 'CASE_OWNER') then 1 when (cas.cat = 'WIFE') then 2 when (cas.cat = 'CHILD') then 3 end)`, cas.birth DESC  (actual time=1.659..1.668 rows=22 loops=1)
    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1)
        -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=1.629..1.635 rows=22 loops=1)
            -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id )   (actual time=1.374..1.577 rows=22 loops=1)
                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1)
                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=1.328..1.337 rows=22 loops=1)
                        -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=1.138..1.292 rows=22 loops=1)
                            -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1)
                                -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=1.101..1.110 rows=22 loops=1)
                                    -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.923..1.075 rows=22 loops=1)
                                        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.006 rows=22 loops=1)
                                            -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.909..0.916 rows=22 loops=1)
                                                -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id )   (actual time=0.784..0.898 rows=22 loops=1)
                                                    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1)
                                                        -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.762..0.769 rows=22 loops=1)
                                                            -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=0.647..0.751 rows=22 loops=1)
                                                                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1)
                                                                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.633..0.640 rows=22 loops=1)
                                                                        -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.524..0.624 rows=22 loops=1)
                                                                            -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1)
                                                                                -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.511..0.518 rows=22 loops=1)
                                                                                    -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=0.383..0.499 rows=22 loops=1)
                                                                                        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1)
                                                                                            -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.370..0.377 rows=22 loops=1)
                                                                                                -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.273..0.361 rows=22 loops=1)
                                                                                                    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1)
                                                                                                        -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.261..0.266 rows=22 loops=1)
                                                                                                            -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.085..0.249 rows=22 loops=1)
                                                                                                                -> Sort: cas.case_id  (cost=2.45 rows=22) (actual time=0.046..0.051 rows=22 loops=1)
                                                                                                                    -> Table scan on cas  (cost=2.45 rows=22) (actual time=0.012..0.031 rows=22 loops=1)



EXPLAIN ANALYZE  -- we see the cas table is scanned 4 times!
SELECT
  s.case_id,
  s.cat,
  s.birth,
  CASE
  
  
    WHEN s.c_cnt = 1
      THEN
        CASE
          WHEN s.a_min >= 21
            THEN 'SINGLE PERSON'
          ELSE 'UNKNOWN'
        END
        
    WHEN s.c_cnt = 2  AND s.w_cnt = 1
      THEN 'PAIR_NO_CHILD'
        
        
    WHEN s.c_cnt >= 3 AND s.w_cnt = 1
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END          
        
        
    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 21 
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END                 
        
        
    ELSE 'No assigned type!'
    
  

  END AS c_t
FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
    
  (SELECT FLOOR(MIN(DATEDIFF(NOW(), c4.birth) / 365.25)) 
     FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min    
    
       
  FROM cas c1
  ORDER BY 
    c1.case_id,
    CASE
      WHEN c1.cat = 'CASE_OWNER' THEN 1
      WHEN c1.cat = 'WIFE'       THEN 2
      WHEN c1.cat = 'CHILD'      THEN 3
    END,
  c1.birth DESC
) AS s;


EXPLAIN


-> Table scan on s  (cost=0.13..2.77 rows=22) (actual time=0.000..0.003 rows=22 loops=1)
    -> Materialize  (cost=4.78..7.43 rows=22) (actual time=4.470..4.473 rows=22 loops=1)
        -> Sort: c1.case_id, (case when (c1.cat = 'CASE_OWNER') then 1 when (c1.cat = 'WIFE') then 2 when (c1.cat = 'CHILD') then 3 end), c1.birth DESC  (cost=2.45 rows=22) (actual time=0.110..0.134 rows=22 loops=1)
            -> Table scan on c1  (cost=2.45 rows=22) (actual time=0.012..0.080 rows=22 loops=1)
        -> Select #3 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.045..0.046 rows=1 loops=22)
                    -> Filter: (c2.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.013..0.026 rows=3 loops=22)
                        -> Table scan on c2  (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22)
        -> Select #4 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.049..0.049 rows=1 loops=22)
                    -> Filter: (c3.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.014..0.027 rows=3 loops=22)
                        -> Table scan on c3  (cost=0.47 rows=22) (actual time=0.007..0.024 rows=22 loops=22)
        -> Select #5 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.056..0.056 rows=1 loops=22)
                    -> Filter: (c4.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.013..0.026 rows=3 loops=22)
                        -> Table scan on c4  (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22)



EXPLAIN ANALYZE
SELECT
  case_id,
  cat,
  birth,
  c_type,
  CASE
  
-- 1st section:
  

  
    WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1 
      THEN 
        CASE
          WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
            THEN 'SINGLE_PERSON'
          ELSE 'UNKNOWN'
        END
    
    
-- 2nd section:
  

    WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2 
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN 'PAIR_NO_CHILD'
    

-- 3rd section:


    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END  

    
-- 4th section:


    WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 2
    AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 0
      THEN
        CASE
          WHEN MIN((DATEDIFF(NOW(), birth) / 365.25)) OVER (PARTITION BY case_id) < 21
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END 
  
    ELSE 'No assigned type'
    
    
  END AS c_t

  -- 5th section: 

FROM
  cas;
 
  
  


EXPLAIN


-> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id )   (actual time=1.420..1.596 rows=22 loops=1)
    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1)
        -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=1.378..1.386 rows=22 loops=1)
            -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=1.199..1.339 rows=22 loops=1)
                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1)
                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=1.141..1.150 rows=22 loops=1)
                        -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=1.008..1.126 rows=22 loops=1)
                            -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1)
                                -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.981..0.988 rows=22 loops=1)
                                    -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id )   (actual time=0.851..0.965 rows=22 loops=1)
                                        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1)
                                            -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.827..0.835 rows=22 loops=1)
                                                -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=0.708..0.812 rows=22 loops=1)
                                                    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1)
                                                        -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.687..0.694 rows=22 loops=1)
                                                            -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.573..0.672 rows=22 loops=1)
                                                                -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1)
                                                                    -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.551..0.559 rows=22 loops=1)
                                                                        -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id )   (actual time=0.444..0.538 rows=22 loops=1)
                                                                            -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1)
                                                                                -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.422..0.429 rows=22 loops=1)
                                                                                    -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.312..0.401 rows=22 loops=1)
                                                                                        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1)
                                                                                            -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.286..0.293 rows=22 loops=1)
                                                                                                -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id )   (actual time=0.080..0.274 rows=22 loops=1)
                                                                                                    -> Sort: cas.case_id  (cost=2.45 rows=22) (actual time=0.045..0.051 rows=22 loops=1)
                                                                                                        -> Table scan on cas  (cost=2.45 rows=22) (actual time=0.012..0.030 rows=22 loops=1)



EXPLAIN ANALYZE
SELECT
  s.case_id,
  s.cat,
  s.birth,
  CASE
  
    WHEN s.c_cnt = 1
      THEN
        CASE
          WHEN s.a_min >= 21
            THEN 'SINGLE PERSON'
          ELSE 'UNKNOWN'
        END
        
    WHEN s.c_cnt = 2  AND s.w_cnt = 1
      THEN 'PAIR_NO_CHILD'
        
        
    WHEN s.c_cnt >= 3 AND s.w_cnt = 1
      THEN
        CASE
          WHEN s.a_min < 21
            THEN 'PAIR_WITH_CHILD'
          ELSE 'OTHER'
        END          
        
        
    WHEN s.c_cnt >= 2 AND s.w_cnt = 0
      THEN
        CASE
          WHEN s.a_min < 21 
            THEN 'SINGLE_WITH_CHILD'
          ELSE 'MULTIPLE'
        END                 
        
        
    ELSE 'No assigned type!'

  END AS c_t
  
FROM
(
  SELECT 
    case_id,
    cat, 
    birth,
    (SELECT COUNT(c2.case_id) 
       FROM cas c2 WHERE c2.case_id = c1.case_id GROUP BY c2.case_id) AS c_cnt,
    (SELECT SUM(CASE WHEN c3.cat = 'WIFE' THEN 1 ELSE 0 END)     
       FROM cas c3 WHERE c3.case_id = c1.case_id GROUP BY c3.case_id) AS w_cnt,
       
    
  (SELECT FLOOR(MIN(DATEDIFF(NOW(), c4.birth) / 365.25)) 
     FROM cas c4 WHERE c4.case_id = c1.case_id GROUP BY c4.case_id) AS a_min    
    
       
  FROM cas c1
) AS s;



EXPLAIN


-> Table scan on s  (cost=0.13..2.77 rows=22) (actual time=0.001..0.003 rows=22 loops=1)
    -> Materialize  (cost=4.78..7.43 rows=22) (actual time=4.827..4.831 rows=22 loops=1)
        -> Table scan on c1  (cost=2.45 rows=22) (actual time=0.011..0.044 rows=22 loops=1)
        -> Select #3 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.045..0.045 rows=1 loops=22)
                    -> Filter: (c2.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.013..0.025 rows=3 loops=22)
                        -> Table scan on c2  (cost=0.47 rows=22) (actual time=0.006..0.022 rows=22 loops=22)
        -> Select #4 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.050..0.050 rows=1 loops=22)
                    -> Filter: (c3.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.013..0.027 rows=3 loops=22)
                        -> Table scan on c3  (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22)
        -> Select #5 (subquery in projection; dependent)
            -> Table scan on <temporary>  (actual time=0.000..0.000 rows=1 loops=22)
                -> Aggregate using temporary table  (actual time=0.075..0.075 rows=1 loops=22)
                    -> Filter: (c4.case_id = c1.case_id)  (cost=0.47 rows=2) (actual time=0.033..0.046 rows=3 loops=22)
                        -> Table scan on c4  (cost=0.47 rows=22) (actual time=0.025..0.043 rows=22 loops=22)



-- 
-- Rabbit hole! Hic sunt leones!
--

--
-- You might want to look at the ORDER BY in the LEAD() function - another 
-- window function! Discarded, but maybe one to keep in mind?
--

SELECT
  case_id,
  cat,
  birth,
  c_type,
 
 
--
-- Note the use of the LEAD() window function - eventually discarded in this case
-- but can be very useful in other scenarios!
--

  CASE
    WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 2 THEN
      CASE
        WHEN (cat IN ('CASE_OWNER')) 
          AND 
            LEAD(cat) 
              OVER (PARTITION BY case_id
              
                              ORDER BY 
                                CASE
                                  WHEN cat = 'CASE_OWNER' THEN 1
                                  WHEN cat = 'WIFE'       THEN 2
                                  WHEN cat = 'CHILD'      THEN 3
                              END              
              
                   ) = 'WIFE' 
          THEN 'PAIR_NO_CHILD'
        WHEN cat = 'WIFE' 
          THEN 'PAIR_NO_CHILD'
     

     END

    ELSE 'YYYY'

  END AS c_t
  
  -- AGE(birth) AS age_now,
  

FROM
  cas
ORDER BY 
  case_id,
  CASE
    WHEN cat = 'CASE_OWNER' THEN 1
    WHEN cat = 'WIFE'       THEN 2
    WHEN cat = 'CHILD'      THEN 3
  END,
  birth DESC;


case_id
cat
birth
c_type
c_t


20033738
CASE_OWNER
1996-04-08
null
PAIR_NO_CHILD

20033738
WIFE
1995-08-22
null
PAIR_NO_CHILD

20033831
CASE_OWNER
1975-03-05
null
YYYY

20033831
CHILD
2013-10-25
null
YYYY

20033831
CHILD
2010-05-20
null
YYYY

20033831
CHILD
2006-03-25
null
YYYY

20033831
CHILD
2005-03-19
null
YYYY

20039301
CASE_OWNER
1999-07-27
null
YYYY

20039301
WIFE
2001-07-05
null
YYYY

20039301
CHILD
2018-10-22
null
YYYY

20039334
CASE_OWNER
1994-03-10
null
YYYY

30033333
CASE_OWNER
1980-01-01
null
null

30033333
CHILD
2012-09-01
null
null

30044444
CASE_OWNER
2015-08-10
null
YYYY

30055555
CASE_OWNER
1970-02-10
null
YYYY

30055555
WIFE
1972-07-05
null
YYYY

30055555
CHILD
1997-05-19
null
YYYY

30055555
CHILD
1995-11-22
null
YYYY

30066666
CASE_OWNER
1970-02-10
null
YYYY

30066666
CHILD
1994-05-19
null
YYYY

30066666
CHILD
1992-11-22
null
YYYY

30066666
CHILD
1989-07-05
null
YYYY
case_id	cat	birth	c_type	c_t
20033738	CASE_OWNER	1996-04-08	null	PAIR_NO_CHILD
20033738	WIFE	1995-08-22	null	PAIR_NO_CHILD
20033831	CASE_OWNER	1975-03-05	null	SINGLE_WITH_CHILD
20033831	CHILD	2013-10-25	null	SINGLE_WITH_CHILD
20033831	CHILD	2010-05-20	null	SINGLE_WITH_CHILD
20033831	CHILD	2006-03-25	null	SINGLE_WITH_CHILD
20033831	CHILD	2005-03-19	null	SINGLE_WITH_CHILD
20039301	CASE_OWNER	1999-07-27	null	PAIR_WITH_CHILD
20039301	WIFE	2001-07-05	null	PAIR_WITH_CHILD
20039301	CHILD	2018-10-22	null	PAIR_WITH_CHILD
20039334	CASE_OWNER	1994-03-10	null	SINGLE_PERSON
30033333	CASE_OWNER	1980-01-01	null	SINGLE_WITH_CHILD
30033333	CHILD	2012-09-01	null	SINGLE_WITH_CHILD
30044444	CASE_OWNER	2015-08-10	null	UNKNOWN
30055555	CASE_OWNER	1970-02-10	null	OTHER
30055555	WIFE	1972-07-05	null	OTHER
30055555	CHILD	1997-05-19	null	OTHER
30055555	CHILD	1995-11-22	null	OTHER
30066666	CASE_OWNER	1970-02-10	null	MULTIPLE
30066666	CHILD	1994-05-19	null	MULTIPLE
30066666	CHILD	1992-11-22	null	MULTIPLE
30066666	CHILD	1989-07-05	null	MULTIPLE