MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE demo (id INT, user_id INT, title VARCHAR(255), data VARCHAR(255));
INSERT INTO demo VALUES
(1, 1, 'DATE', '01-01-2021'),
(2, 1, 'COLOR', 'RED'),
(3, 2, 'DATE', NULL),
(4, 2, 'COLOR', 'YELLOW'),
(5, 3, 'DATE', '04-01-2021'),
(6, 3, 'COLOR', 'RED'),
(5, 4, 'DATE', '04-01-2021'),
(6, 4, 'COLOR', 'PINK'),
(7, 5, 'DATE', '02-01-2021'),
(8, 5, 'COLOR', 'PINK'),
(7, 6, 'DATE', '08-01-2021'),
(8, 6, 'COLOR', 'PINK'),
(3,	7,	'DATE',	   '07-07-2027'), 
(4,	7,	'COLOR',  'YELLOW');
SELECT * FROM demo;


id
user_id
title
data


1
1
DATE
01-01-2021

2
1
COLOR
RED

3
2
DATE
null

4
2
COLOR
YELLOW

5
3
DATE
04-01-2021

6
3
COLOR
RED

5
4
DATE
04-01-2021

6
4
COLOR
PINK

7
5
DATE
02-01-2021

8
5
COLOR
PINK

7
6
DATE
08-01-2021

8
6
COLOR
PINK

3
7
DATE
07-07-2027

4
7
COLOR
YELLOW


SELECT
CAST(
      CONCAT
      (
        SUBSTRING(data, 7, 10), 
        SUBSTRING(data, 3, 4), 
        SUBSTRING(data, 1, 2)
      ) AS DATE
    ) AS z

FROM demo y
WHERE title = 'DATE';


z


2021-01-01

null

2021-01-04

2021-01-04

2021-01-02

2021-01-08

2027-07-07


CREATE VIEW demo_view AS
(
  SELECT 
    x.user_id AS "uid", 
    x.data AS "color", 
    CAST(
          CONCAT
          (
            SUBSTRING(y.data, 7, 10), 
            SUBSTRING(y.data, 3, 4), 
            SUBSTRING(y.data, 1, 2)
          ) AS DATE
        ) AS c_date
  FROM demo x
  JOIN demo y
    ON x.user_id = y.user_id
    AND x.title = 'COLOR' 
    AND y.title = 'DATE'
);
CREATE TABLE demo_tab AS
(
  SELECT * FROM demo_view
  
  --
  -- Could also construct it as above!
  -- 
);
SELECT * FROM demo_view;


uid
color
c_date


1
RED
2021-01-01

2
YELLOW
null

3
RED
2021-01-04

4
PINK
2021-01-04

5
PINK
2021-01-02

6
PINK
2021-01-08

7
YELLOW
2027-07-07


SELECT * FROM demo_tab;


uid
color
c_date


1
RED
2021-01-01

2
YELLOW
null

3
RED
2021-01-04

4
PINK
2021-01-04

5
PINK
2021-01-02

6
PINK
2021-01-08

7
YELLOW
2027-07-07


SET PROFILING = 1;
SELECT 
  dt.color AS "Color", 
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color;


Color
Collected
Total


PINK
3
3

RED
2
2

YELLOW
1
2


SELECT 
  dt.color AS "Color", 
  SUM(CASE
        WHEN dt.c_date IS NOT NULL
      THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color;


Color
Collected
Total


PINK
3
3

RED
2
2

YELLOW
1
2


SELECT 
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) Total,

ROUND(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1.0
      ELSE 0.0
    END 
   ) * 100 / COUNT(*), 2) AS "% collected"

FROM demo t1
JOIN demo t2 ON t1.user_id = t2.user_id
WHERE t1.title = 'DATE'
  AND t2.title = 'COLOR'
GROUP BY t2.data
ORDER BY 1;


Color
Collected
Total
% collected


PINK
3
3
100.00

RED
2
2
100.00

YELLOW
1
2
50.00


SELECT 
  v.color AS "Color", 
  SUM(CASE
      WHEN v.c_date IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_view v
GROUP BY v.color
ORDER BY 1;


Color
Collected
Total


PINK
3
3

RED
2
2

YELLOW
1
2


SHOW PROFILES;


Query_ID
Duration
Query


1
0.00052950
SELECT 
  dt.color AS "Color", 
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color

2
0.00053850
SELECT 
  dt.color AS "Color", 
  SUM(CASE
        WHEN dt.c_date IS NOT NULL
      THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color

3
0.00066150
SELECT 
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) Total,

ROUND(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1.0
      ELSE 0.0
    END 
   ) * 100 / COUNT(*), 2) AS "% collected"

FROM demo t1
JOIN demo t2 ON t1.user_id

4
0.00070100
SELECT 
  v.color AS "Color", 
  SUM(CASE
      WHEN v.c_date IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_view v
GROUP BY v.color
ORDER BY 1


SET PROFILING = 0;
EXPLAIN ANALYZE
SELECT 
  dt.color AS "Color", 
  SUM(CASE
      WHEN dt.c_date IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color;


EXPLAIN


-> Table scan on <temporary>  (actual time=0.000..0.001 rows=3 loops=1)
    -> Aggregate using temporary table  (actual time=0.049..0.050 rows=3 loops=1)
        -> Table scan on dt  (cost=0.95 rows=7) (actual time=0.011..0.023 rows=7 loops=1)



EXPLAIN ANALYZE
SELECT 
  v.color AS "Color", 
  SUM(CASE
      WHEN v.c_date IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) AS "Total"
FROM demo_view v
GROUP BY v.color;


EXPLAIN


-> Table scan on <temporary>  (actual time=0.001..0.001 rows=3 loops=1)
    -> Aggregate using temporary table  (actual time=0.118..0.118 rows=3 loops=1)
        -> Inner hash join (y.user_id = x.user_id)  (cost=3.32 rows=0) (actual time=0.049..0.078 rows=7 loops=1)
            -> Filter: (y.title = 'DATE')  (cost=1.16 rows=1) (actual time=0.005..0.029 rows=7 loops=1)
                -> Table scan on y  (cost=1.16 rows=14) (actual time=0.004..0.023 rows=14 loops=1)
            -> Hash
                -> Filter: (x.title = 'COLOR')  (cost=1.65 rows=1) (actual time=0.017..0.032 rows=7 loops=1)
                    -> Table scan on x  (cost=1.65 rows=14) (actual time=0.012..0.027 rows=14 loops=1)



EXPLAIN ANALYZE
SELECT 
  t2.data AS "Color",
SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS "Collected",
COUNT(*) Total,
CAST(SUM(CASE
      WHEN t1.data IS NOT NULL THEN 1
      ELSE 0
    END 
   ) AS FLOAT) * 100 / COUNT(*) AS "% collected"
FROM demo t1
JOIN demo t2 USING (user_id)
WHERE t1.title = 'DATE'
  AND t2.title = 'COLOR'
GROUP BY t2.data
ORDER BY 1;


EXPLAIN


-> Sort: t2.`data`  (actual time=0.127..0.127 rows=3 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.001 rows=3 loops=1)
        -> Aggregate using temporary table  (actual time=0.106..0.107 rows=3 loops=1)
            -> Inner hash join (t2.user_id = t1.user_id)  (cost=3.32 rows=0) (actual time=0.049..0.075 rows=7 loops=1)
                -> Filter: (t2.title = 'COLOR')  (cost=1.16 rows=1) (actual time=0.006..0.027 rows=7 loops=1)
                    -> Table scan on t2  (cost=1.16 rows=14) (actual time=0.004..0.022 rows=14 loops=1)
                -> Hash
                    -> Filter: (t1.title = 'DATE')  (cost=1.65 rows=1) (actual time=0.014..0.032 rows=7 loops=1)
                        -> Table scan on t1  (cost=1.65 rows=14) (actual time=0.012..0.027 rows=14 loops=1)



EXPLAIN ANALYZE
SELECT 
  dt.color AS "Color", 
  COUNT(dt.c_date) AS "Collected",
  COUNT(*) AS "Total"
FROM demo_tab dt
GROUP BY dt.color
ORDER BY dt.color;


EXPLAIN


-> Sort: dt.color  (actual time=0.075..0.076 rows=3 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.001 rows=3 loops=1)
        -> Aggregate using temporary table  (actual time=0.064..0.065 rows=3 loops=1)
            -> Table scan on dt  (cost=0.95 rows=7) (actual time=0.030..0.041 rows=7 loops=1)



SET sql_mode='ANSI';
SELECT 
  user_id, title, data,
  SUBSTRING(data, 7, 10) || 
  SUBSTRING(data, 3, 4) ||
  SUBSTRING(data, 1, 2) AS w,
  
  CAST(
        SUBSTRING(data, 7, 10) || 
        SUBSTRING(data, 3, 4) ||
        SUBSTRING(data, 1, 2) 
        AS DATE
      )
      AS x
FROM demo 
WHERE title = 'DATE'
ORDER BY user_id, x DESC;


user_id
title
data
w
x


1
DATE
01-01-2021
2021-01-01
2021-01-01

2
DATE
null
null
null

3
DATE
04-01-2021
2021-01-04
2021-01-04

4
DATE
04-01-2021
2021-01-04
2021-01-04

5
DATE
02-01-2021
2021-01-02
2021-01-02

6
DATE
08-01-2021
2021-01-08
2021-01-08

7
DATE
07-07-2027
2027-07-07
2027-07-07

id	user_id	title	data
1	1	DATE	01-01-2021
2	1	COLOR	RED
3	2	DATE	null
4	2	COLOR	YELLOW
5	3	DATE	04-01-2021
6	3	COLOR	RED
5	4	DATE	04-01-2021
6	4	COLOR	PINK
7	5	DATE	02-01-2021
8	5	COLOR	PINK
7	6	DATE	08-01-2021
8	6	COLOR	PINK
3	7	DATE	07-07-2027
4	7	COLOR	YELLOW

uid	color	c_date
1	RED	2021-01-01
2	YELLOW	null
3	RED	2021-01-04
4	PINK	2021-01-04
5	PINK	2021-01-02
6	PINK	2021-01-08
7	YELLOW	2027-07-07

Query_ID	Duration	Query
1	0.00052950	SELECT dt.color AS "Color", COUNT(dt.c_date) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color
2	0.00053850	SELECT dt.color AS "Color", SUM(CASE WHEN dt.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color
3	0.00066150	SELECT t2.data AS "Color", SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT() Total, ROUND(SUM(CASE WHEN t1.data IS NOT NULL THEN 1.0 ELSE 0.0 END ) 100 / COUNT(*), 2) AS "% collected" FROM demo t1 JOIN demo t2 ON t1.user_id
4	0.00070100	SELECT v.color AS "Color", SUM(CASE WHEN v.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_view v GROUP BY v.color ORDER BY 1