Auto Repair - SQL Interview Query 10 | SQL Problem Level "HARD"

[@sujathapullareddy7726]

Simply too good! Thanks Tofiq for all your effort.

[@Savenature635]

Thanks for the question. This #30daysSQLquerychallenge is helping a lot.
Here is my approach
With velocity as (select row_number() over() as rn,value as velocity from auto_repair
where indicator='velocity'),
level as
(select row_number() over() as rn,value as level from auto_repair
where indicator='level')
select v.velocity,
count(case when level='good' then velocity end) as good,
count(case when level='wrong' then velocity end) as wrong,
count(case when level='regular' then velocity end) as regular
from velocity v
join
level l
on v.rn=l.rn
group by 1
order by velocity;

[@haleylearn]

Wow so interesting

[@splendidabhi]

Why i am addicted to this type of questions ??
i am watching all of sql video one by one and learning new things in every video

[@varunas9784]

Great one as always Thoufik!
Here's my approach. Thought I would use string manipulation and NTILE functions for a change ;)
=====================================================
declare @partition int
set @partition = (select COUNT(*) from auto_repair);
with cte as (select NTILE(@partition/2) over(order by (select 1)) grp_num, *
from auto_repair),
cte2 as (select STRING_AGG(value, '-') value_agg
from cte
group by grp_num)
select SUBSTRING(value_agg, CHARINDEX('-', value_agg, 1) + 1, 5) as [Velocity],
COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'good' then 1 else null end) [good],
COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'wrong' then 1 else null end) [wrong],
COUNT(case when (LEFT(value_agg, CHARINDEX('-', value_agg,1) - 1)) = 'regular' then 1 else null end) [regular]
from cte2
group by SUBSTRING(value_agg, CHARINDEX('-', value_agg, 1) + 1, 5)
======================================================

[@ishanshubham8355]

My approach to solve this (Mysql) with cte as (
select m.value as velocity, n.value as level
from
(select *
from auto_repair
where indicator ="level") n
inner join
(select *
from auto_repair
where indicator ="velocity") m
on n.client = m.client and n.auto =m.auto and n.repair_date = m.repair_date)
select velocity,
sum(if(level = "good",1,0))as "good",
sum(if(level= "wrong",1,0)) as "wrong",
sum(if(level= "regular",1,0)) as "wrong"
from cte
group by velocity
order by velocity;

[@easycommerceclasses8241]

thanks

[@saralavasudevan5167]

Hi sir, than you for all the hardwork you put in! This is an amazing series. :)) Below is my approach for this problem :
with mycte as
(
select new_velocity,
case when new_velocity = x.value then (lag(x.value,1) over(partition by client, repair_date order by repair_date)) end as new_val
from
(
select *,
case when indicator = 'velocity' then value else NULL end as new_velocity
from auto_repair
)as x
)
select new_velocity as velocity,
sum(case when new_val = 'good' then 1 else 0 end) as good,
sum(case when new_val = 'wrong' then 1 else 0 end) as wrong,
sum(case when new_val = 'regular' then 1 else 0 end) as regular
from mycte
where new_velocity is not null and new_val is not null
group by new_velocity

[@CebuProvince]

this is the correct solution, I done this at the same way

[@OmkarPatil-r9r]

with cte as (
select client ,repair_date , MAX(CASE WHEN indicator = 'level' then value END) as level ,
MAX(case when indicator = 'velocity' then value end) as velocity
from auto_repair
group by client ,repair_date
)
select velocity ,
COUNT(CASE WHEN level = 'good' then 1 END) as 'Good' ,
COUNT(CASE WHEN level = 'wrong' then 1 END) as 'Wrong',
COUNT(CASE WHEN level = 'regular' then 1 END) as 'Regular'
from cte
group by velocity
you could have optimized it.

[@apppu1k221]

suing MySQL and multiple CTEs
with cte as (
select indicator, value,
lag(value,1) over(partition by client,auto,repair_date order by repair_date) as vel
from auto_repair),
cte1 as(
select * from cte
where vel is not null),
cte2 as(select value, vel, count(*) as num
from cte1
group by value,vel)
select
value ,
max(case when vel = 'good' then num else 0 end) as good,
max(case when vel = 'regular' then num else 0 end) as regular,
max(case when vel = 'wrong' then num else 0 end) as wrong
from cte2
group by value

[@shivinmehta7368]

with cte as (
select value as velocity ,lvl,count(*) as cnt from(
select indicator,value,lag(value) over(order by client , auto,repair_date) as lvl from auto_repair
)x where indicator ='velocity'
group by 1,2
)
select velocity,
sum(case when lvl='good' then cnt else 0 end) as good,
sum(case when lvl='wrong' then cnt else 0 end) as wrong,
sum(case when lvl='regular' then cnt else 0 end) as regular
from cte
group by 1

[@rojas556]

Many thanks Thoufiq! Using MSSQL.

[@growtogether1341]

with outmost as (
with final_cte as (
with cte as (
select concat(client , auto, repair_date) as key, value as val from auto_repair
)
select distinct key, first_value(val) over(partition by key) as name, last_value(val) over(partition by key) as val from cte
)
select val as velocity, case when name='good' then count(name) end as good,
case when name='wrong' then count(name) end as wrong,
case when name='regular' then count(name) end as regular from final_cte
group by val, name
)
select velocity, sum(coalesce(good,0)) as good, sum(coalesce(wrong,0)) as wrong, sum(coalesce(regular,0)) as regular from outmost
group by velocity
order by velocity

[@VijayKumarGode-t1c]

This is another approach for solving the problem
with cte as(
select sum(case when indicator = 'velocity' then value end) velocity,
max(case when indicator = 'level' then value end) levels, repair_date,client,auto from auto_repair
group by repair_date,client,auto)
select velocity,
case when sum(case when levels ='good' then 1 else 0 end) good,
sum(case when levels = 'wrong' then 1 else 0 end) wrong,
sum(case when levels = 'regular' then 1 else 0 end) regular
from cte group by velocity order by velocity;

[@alokkumarsahu3035]

Thank you sir, I want to learn this kind of complex query from you. If you are providing any training then I am eager to join sir

[@VaibhavGupta-j4i]

Solution for MySQL Workbench :-
with cte as(
select v.value as velocity, l.value as level, count(1) as value
from auto_repair l
join auto_repair v
on l.client = v.client and l.auto = v.auto and l.repair_date = v.repair_date
where l.indicator = 'level' and v.indicator = 'velocity'
group by v.value, l.value
order by v.value, l.value
)
select velocity,
sum(case when level = "good" then value else 0 end) as good,
sum(case when level = "wrong" then value else 0 end) as wrong,
sum(case when level = "regular" then value else 0 end) as regular
from cte
group by velocity;

[@Ak12345-g]

Nice problem thanks for giving some tricky 😊questions to slove

[@hrishikeshXXV]

slove

[@Satish_____Sharma]

Here is my approach using mysql
with cte as (SELECT client, auto, repair_date, indicator, value,row_number() over () as rn FROM auto_repair )
,cte1 as (select client, auto, repair_date, indicator, value, rn,lag(value) over (order by rn) as cnt from cte )
select value as Velocity,sum(case when cnt='good' then 1 else 0 end) as good,
sum(case when cnt='wrong' then 1 else 0 end) as wrong,
sum(case when cnt='regular' then 1 else 0 end) as regular
from cte1 where indicator='velocity' group by value order by Velocity

[@Kirankumar-ml1ro]

--Alternative solution without using crosstab
with cte as (
select l.value as level,v.value as velocity
from auto_repair l
join auto_repair v on l.client=v.client and l.auto=v.auto and l.repair_Date=v.repair_date
where l.indicator='level' and v.indicator='velocity'
)
select velocity,
sum(case when level='good' then 1 else 0 end) as good,
sum(case when level='wrong' then 1 else 0 end) as wrong,
sum(case when level='regular' then 1 else 0 end) as regular
from cte
group by velocity

[@RABBANIBAJISHAIK]

Thanks a lot , Sir

[@sass520]

Hello sir , In Joins , how to know, when to use 1 condition or when to use 3 conditions?
Like here you joined level and Velocity Table by Self join auto=auto, client=client, repair date= repair date. How to figure out this when to use 3 columns for condition 6:50. Please reply 🙏

[@AbhishekSharma-dn5us]

we have to check how we can make a table row unqiue. If we select only 1 or 2 column it would have made records not unique so we are using 3 columns

[@juanromantorres5063]

Wow thank You!

[@iswillia123]

using Oracle:
select
listagg(case indicator when 'velocity' then value else null end, '') as velocity,
listagg(case indicator when 'level' then value else null end, '') as lev
from auto_repair
group by client, repair_date
)
select *
from cte
pivot (
count(lev) for lev in ('good' good, 'wrong' wrong, 'regular' regular ))
order by velocity;

[@Damon-007]

My solution -MSSQL
with cte as(
select indicator,value,lag(value) over(partition by client, auto, repair_date
order by (select null)
) val from auto_repair)
select value,
sum(iif(val= 'good',1,0)) good,
sum(iif(val = 'wrong', 1,0)) wrong ,
sum(iif(val = 'regular',1, 0)) regular
from cte
where val is not null
group by value;

[@MdZeeshan-m9u]

Thank You so much

[@MohitYadav-hz8tb]

with a as(select *,lag(value) over(partition by client,auto,repair_date) as value2
from auto_repair)
select value,
sum(case when value2 = "good" then 1 end) as good,
sum(case when value2 = "regular" then 1 end) as regular,
sum(case when value2 = "wrong"then 1 end) as wrong
from a
where indicator = "velocity"
group by 1;

[@fathimafarahna2633]

❤❤

[@arvindkr974]

with cte1 as
(select level,velocity from auto_repair
pivot(
max(value) for indicator in (level,velocity)
)piv),
cte2 as(
select velocity, level, count(*) as cn from cte1 group by level,velocity)
select velocity, coalesce(good,0) as good, coalesce(wrong,0) as wrong, coalesce(regular,0) as regular from cte2
pivot(
max(cn) for cte2.level
in (good, wrong,regular)
) piv2

[@krishanukundu4565]

My simple approach-
with indicator_cte as (
select l.value as level, v.value as velocity
from `im-tesseract-dev.stud.auto_repair` l
join `im-tesseract-dev.stud.auto_repair` v
on l.client= v.client and l.auto= v.auto and l.repair_date= v.repair_date
where l.indicator= 'level' and v.indicator= 'velocity')
select
velocity,
sum(case when level= 'good' then 1 else 0 end) as good,
sum(case when level= 'wrong' then 1 else 0 end) as wrong,
sum(case when level= 'regular' then 1 else 0 end) as regular
from indicator_cte
group by 1
order by 1

[@radhikamaheshwari4835]

with cte1 as
(
select client, auto, repair_date,
min(case when indicator = 'level' then value end) as indi,
min(case when indicator = 'velocity' then value end) as velocity
from auto_repair
group by client, auto, repair_date
)
select velocity,
sum(case when indi = 'good' then 1 else 0 end) as good,
sum(case when indi = 'wrong' then 1 else 0 end) as wrong,
sum(case when indi = 'regular' then 1 else 0 end) as regular
from cte1
group by velocity

[@ashutoshpratapsingh6872]

Thanks

[@sagarsaini5447]

solved in mysql
with cte as
(select v.value as velocity, l.value as level
from auto_repair l
join auto_repair v
on l.client=v.client and l.auto=v.auto and l.repair_date=v.repair_date
where l.indicator='level' and v.indicator='velocity')
select velocity,
sum(case when level="good" then 1 else 0 end) as Good,
sum(case when level="wrong" then 1 else 0 end) as Wrong,
sum(case when level="Regular" then 1 else 0 end) as Regular
from cte
group by velocity
order by velocity

[@Löwen_av]

with cte as
(select velocity, level, count(1) as val from auto_repair pivot
(max(value) for indicator in ([velocity],[level])) p
group by velocity,level),
cte2 as (select velocity, good, wrong, regular
from cte pivot (max(val) for level in ([good],[wrong],[regular]) ) p
)
select velocity, coalesce(good,0) as good, coalesce(wrong,0) as wrong, coalesce(regular,0) as regular
from cte2

[@niteshtiwari-uk7qq]

I tried on MY SQL @MY Sql Solution :
with cte as
(select V.value as velocity, l.value as level from auto_repair l
join auto_repair V
on l.client=V.client and l.auto=V.auto and l.repair_date=V.repair_date
where l.indicator='level' and V.indicator='velocity')
select velocity,
count(CASE WHEN level = 'good' THEN velocity END) AS good,
count(CASE WHEN level = 'wrong' THEN velocity END) AS wrong,
count(CASE WHEN level = 'regular' THEN velocity END) AS regular
from cte
group by velocity
order by velocity;

[@rockykhanna7518]

@techTFQ How about this approach?
with cte as (
select client, auto, repair_date,
max(case when indicator='velocity' then value end) as velocity,
max(case when value in ('good','regular','wrong') then value end) as level
from auto_repair
group by client, auto, repair_date
)
select velocity,
sum(case when level='good' then 1 else 0 end) as good,
sum(case when level='wrong' then 1 else 0 end) as wrong,
sum(case when level='regular' then 1 else 0 end) as regular
from cte
group by velocity
order by velocity;

[@sahilummat8555]

;with cte as (
select *,row_number()over(order by (select null)) as rn
from auto_repair),cte2 as (
select *,
case when indicator='velocity' then lag(value)over(order by rn ) else null end as vel_val
from cte )
select value as velocity,
sum(case when vel_val='good' then 1 else 0 end ) as good,
sum(case when vel_val='wrong' then 1 else 0 end ) as wrong,
sum(case when vel_val='regular' then 1 else 0 end ) as regular
from cte2 where vel_val is not null
group by value

[@KesavanB-j7s]

Here it's my approach in MSSQL...
WITH
LEVEL AS (SELECT * FROM AUTO_REPAIR WHERE INDICATOR='LEVEL'),
VELOCITY AS (SELECT * FROM AUTO_REPAIR WHERE INDICATOR='VELOCITY'),
JOINS AS(SELECT L.*,V.VALUE AS VELOCITY_VALUE FROM LEVEL L JOIN VELOCITY V
ON L.CLIENT=V.CLIENT
AND L.AUTO=V.AUTO
AND L.REPAIR_DATE=V.REPAIR_DATE),
CATEGORY AS (
SELECT *,
CASE WHEN VALUE='GOOD' THEN 1 ELSE 0 END AS GOOD,
CASE WHEN VALUE='REGULAR' THEN 1 ELSE 0 END AS REGULAR,
CASE WHEN VALUE='WRONG' THEN 1 ELSE 0 END AS WRONG
FROM [JOINS])
SELECT VELOCITY_VALUE,sum(GOOD),sum(REGULAR),sum(WRONG) FROM CATEGORY
GROUP BY VELOCITY_VALUE

[@Alexpudow]

select velocity
,count(case level when 'good' then 1 end) good
,count(case level when 'wrong' then 1 end) wrong
,count(case level when 'regular' then 1 end) regular
from (
select a.value velocity, b.value level
from auto_repair a
join
(select * from auto_repair
where indicator like 'level') b
on a.client=b.client and a.auto=b.auto and a.repair_date = b.repair_date
where a.indicator like 'velocity') t
group by velocity

[@karangupta_DE]

with cte as (
select *
from auto_repair
pivot(max(value) for indicator in ('level', 'velocity'))
as p(client, auto, repair_date, level, velocity)
order by client, auto
),
cte1 as (
select *,
case when level = 'good' then 'good' else null end as good,
case when level = 'wrong' then 'wrong' else null end as wrong,
case when level = 'regular' then 'regular' else null end as regular
from cte
)
select velocity, count(good) as good, count(wrong) as wrong, count(regular) as regular
from cte1
group by velocity
order by velocity;

[@sapnasaini851]

MySQL Solution -
with cte as (
select v.value velocity, l.value level, count(v.value) as cnt
from auto_repair v
join auto_repair l
on v.client = l.client and v.repair_date = l.repair_date and v.auto = l.auto
where v.indicator = 'velocity' and l.indicator = 'level'
group by v.value,l.value
)
select velocity,
sum(case when level = 'good' then cnt else 0 end ) as good,
sum(case when level = 'wrong' then cnt else 0 end) as wrong,
sum(case when level = 'regular' then cnt else 0 end ) as regular
from cte
group by velocity
order by velocity

[@abhishek_suren]

my logic goes like:
with cte as
(select client, auto, repair_date, group_concat(value) as value
from repair_day_10 r
group by client, auto, repair_date),
cte_2 as
(select *, SUBSTRING_INDEX(value, ',', -1) as num
from cte)
select num as velocity,
sum(case when value like "good%" then 1 else 0 end) as good,
sum(case when value like "wrong%" then 1 else 0 end) as wrong,
sum(case when value like "regular%" then 1 else 0 end) as regular
from cte_2
group by 1
order by 1;

[@harshkumargupta1348]

with cte as
(
select t2.value as velocity,t1.value as level from auto_repair t1
join auto_repair t2
on t1.client=t2.client and t1.auto=t2.auto and t1.repair_date=t2.repair_date
where t1.indicator='level' and t2.indicator='velocity')
select velocity,
sum(case when level='good' Then 1 else 0 end) as good,
sum(case when level='wrong' Then 1 else 0 end) as wrong,
sum(case when level='regular' Then 1 else 0 end) as regular
from cte
group by velocity

[@sourabhkumar6811]

select velocity,sum(good)as good,sum(wrong)as wrong,sum(regular)as regular from
(select ar.client,ar.auto,ar.repair_date,
max(case when indicator='velocity' then value end) as velocity,
sum(case when value='good' then 1 else 0 end) as good,
sum(case when value='wrong' then 1 else 0 end) as wrong,
sum(case when value='regular' then 1 else 0 end) as regular
from auto_repair ar
group by client,auto,repair_date)A
group by velocity
order by velocity;

[@naveenvjdandhrudu5141]

-- How to create second table from first table(image attached) client, auto, repair_date, indicator, value
WITH CTE AS (
select V. value AS VELOCITY, L. value from auto_repair L JOIN auto_repair V ON L.client= V.client AND L. auto= V. auto AND L. repair_date= V.repair_date
WHERE L.indicator= "LEVEL" AND V.indicator='velocity'),
CTEA AS (
SELECT VELOCITY, value, count(value) AS REPTS
FROM CTE group by VELOCITY, value order by VELOCITY, value)
SELECT VELOCITY,
MAX(CASE WHEN value= "wrong" THEN REPTS ELSE 0 END) AS wrong,
MAX(CASE WHEN value= "good" THEN REPTS ELSE 0 END) AS good,
MAX(CASE WHEN value= "regular" THEN REPTS ELSE 0 END) AS regular
FROM CTEA GROUP BY VELOCITY;

[@kevinwtao5321]

using the T-SQL function looks more better...

[@brownwolf05]

we can avoid doing the join with the help of window function as this can improve the performance, my approach is below
-----mysql approach
with cte as (
select *, lag(value) over(order by client) as 'level' from auto_repair
),
cte_final as (
select value as velocity, sum(case when level = 'good' then 1 else 0 end) as good,
sum(case when level = 'wrong' then 1 else 0 end) as wrong,
sum(case when level = 'regular' then 1 else 0 end) as regular
from cte where indicator='velocity' group by 1 order by 1
)
select * from cte_final;

[@haleylearn]

-- SOLUTION1: Using GROUPBY, MAX(), SUM()
SELECT velocity
, SUM(CASE WHEN [level] = 'good' THEN 1 ELSE 0 END) AS 'good'
, SUM(CASE WHEN [level] = 'wrong' THEN 1 ELSE 0 END ) AS 'wrong'
, SUM(CASE WHEN [level] = 'regular' THEN 1 ELSE 0 END ) AS 'regular'
FROM
(
SELECT client, auto, repair_date
, MAX(CASE WHEN indicator = 'level' THEN value END) AS level
, MAX(CASE WHEN indicator = 'velocity' THEN value END) AS velocity
FROM auto_repair
GROUP BY client, auto, repair_date
) subquery
GROUP BY velocity;
-- SOLUTION 2: Using INNER JOIN and SUM(CASE WHEN), GROUPBY
SELECT v.[value]
, SUM(CASE WHEN l.[value] = 'good' THEN 1 ELSE 0 END) AS 'good'
, SUM(CASE WHEN l.[value] = 'wrong' THEN 1 ELSE 0 END) AS 'wrong'
, SUM(CASE WHEN l.[value] = 'regular' THEN 1 ELSE 0 END) AS 'regular'
FROM auto_repair l
JOIN auto_repair v
ON l.client = v.client AND l.[auto] = v.[auto] AND l.repair_date = v.repair_date
WHERE l.indicator = 'level' AND v.indicator = 'velocity'
GROUP BY v.[value];
-- SOLUTION 3: Using LEAD(), ROW_NUMBER(), COUNT()
SELECT value_lead
, COUNT(CASE WHEN value = 'good' THEN 1 END) AS 'good'
, COUNT(CASE WHEN value = 'wrong' THEN 1 END) AS 'wrong'
, COUNT(CASE WHEN value = 'regular' THEN 1 END) AS 'regular'
FROM
(
SELECT *
, LEAD(value) OVER(PARTITION BY client, auto, repair_date ORDER BY client) AS value_lead
, ROW_NUMBER() OVER( PARTITION BY client, auto, repair_date ORDER BY client) AS rn
FROM auto_repair
) table_x
WHERE rn = 1
GROUP BY value_lead;

[@likinponnanna8990]

Here's my solution
WITH VELOCITY AS (
SELECT * FROM auto_repair
WHERE INDICATOR = 'velocity'),
level as (
SELECT * FROM auto_repair
WHERE indicator = 'level'
)
SELECT
A.VALUE,
sum(CASE WHEN B.VALUE = 'good' then 1 else 0 end) as good,
sum(CASE WHEN B.VALUE = 'wrong' then 1 else 0 end) as wrong,
sum(case when B.VALUE = 'regular' then 1 else 0 end) as regular
FROM VELOCITY A
JOIN LEVEL B ON A.CLIENT = B.CLIENT AND A.AUTO=B.AUTO AND A.REPAIR_DATE = B.REPAIR_DATE
GROUP BY A.VALUE

[@CebuProvince]

Hi, in your download file is it a little bit different from your video presentaion, in the your file is the field "Count" listet, and this makes a wrong result. Can u check it please. The results a different as your online presentation. Ty. 😇
-- Solution using PIVOT in Micrososft SQLServer
select *
from
(
select v.value velocity, l.value level,count(1) as count😇
from auto_repair l
join auto_repair v on v.auto=l.auto and v.repair_date=l.repair_date and l.client=v.client
where l.indicator='level'
and v.indicator='velocity'
group by v.value,l.value
) bq
pivot
(
count(level)
for level in ([good],[wrong],[regular])
) pq;
velocity count good wrong regular
50 1 0 1 0
70 1 0 1 0
80 1 1 0 1
90 2 1 0 0

[@kanikamittal7349]

You should use SUM(count) in the pivot query pq. It would give the correct result then. I have solved in similar way. It would be good if you change the name of 3 column in base query to something else since COUNT is a pre defined function

[@CebuProvince]

Yes, Sum is the solution, look in the code of
@saralavasudevan5167, she offer the correct solution of the task #11

[@rohit_vora]

hello everyone, here i share my query which is same as thoufiq but only diffrence is in the second argument in the crosstab function, and i don't know why i am not getting same ans as him insted i am getting some erorr. if any one could assist me that would be greatfull.
SELECT *
FROM crosstab(
'SELECT v.value AS velocity, l.value AS level, COUNT(1) AS cnt
FROM auto_repair l
JOIN auto_repair v ON v.client = l.client AND l.auto = v.auto AND l.repair_date = v.repair_date
WHERE l.indicator = ''level'' AND v.indicator = ''velocity''
GROUP BY v.value, l.value
ORDER BY v.value' ,
('good', 'regular', 'wrong')
) AS result(velocity VARCHAR, good BIGINT, regular BIGINT, wrong BIGINT);

[@kidsRockss]

with cte as (
select value,
row_number() over() as count
from auto_repair
where indicator = 'velocity'),
cte1 as (select value,
row_number() over() as count
from auto_repair
where indicator = 'level')
select c.value as velocity,
count(case when c1.value = 'good' then c.value end) as 'good',
count(case when c1.value = 'wrong' then c.value end) as 'wrong',
count(case when c1.value = 'regular' then c.value end) as 'regular'
from cte c
join cte1 c1 on c.count = c1.count
group by c.value
order by c.value

[@CebuProvince]

The function 'row_number' must have an OVER clause with ORDER BY.

[@camomile4085]

-- MY APPROACH:
------------- 1. CREATE VIEW a table including 3 columns: Value; Level; COUNT(level) GROUP BY Value, Level
CREATE VIEW cte AS (
SELECT value, lag, COUNT(*)
FROM (SELECT *, LAG(value) OVER(PARTITION BY client, repair_date)
FROM input_10)
WHERE indicator = 'velocity'
GROUP BY value, lag)
------------- 2. Use CROSSTAB to PIVOT TABLE
SELECT * FROM crosstab ('SELECT * FROM cte',
'SELECT DISTINCT(lag) FROM cte ORDER BY lag')
AS RESULT (velocity INT, good INT, regular INT, wrong INT)

[@shashwatkandoi5315]

This is the Solution for MYSQL
----------------------------------------------------------------------------------
With CTE AS (
Select V.value as Velocity, L.value as Level, count(1) as Count_Value
from
auto_repair L
join auto_repair V
on L.client = V.client and L.auto = V.auto and L.repair_date = V.repair_date
where L.indicator = 'level' and
V.indicator = 'velocity'
group by V.value, L.value
order by V.value, L.value)
Select
Velocity,
sum(CAST(CASE WHEN Level = 'good' THEN Count_Value ELSE 0 END AS Signed)) AS Good,
sum(CAST(CASE WHEN Level = 'wrong' THEN Count_Value ELSE 0 END AS Signed)) AS Wrong,
sum(CAST(CASE WHEN Level = 'regular' THEN Count_Value ELSE 0 END AS Signed)) AS Regular
from CTE
group by Velocity
order by Velocity