SQL -EX.ru query problem set # 28
I am trying to write a SQL query to resolve a question on www.sql-ex.ru (Q. 28), I got the correct result, but I got this error: "Your query failed in the third database check."
SQL query query:
Within two decimal digits, determine the average amount of paint per square.
The database schema consists of 3 tables:
utQ (Q_ID int, Q_NAME varchar (35)), utV (V_ID int, V_NAME varchar (35), V_COLOR char (1)),
utB (B_Q_ID int, B_V_ID int, B_VOL tinyint, B_DATETIME datetime). The utQ table includes a square identifier, the name of the square. Please note that black is not painted.
utV includes balloon ID, balloon name and paint color. The utB table displays information about drawing a square with a balloon and includes the square ID, balloon ID, paint amount, and paint time.
- It should be noted that the balloon can have one of three colors: red (V_COLOR = 'R'), green (V_COLOR = 'G') or blue (V_COLOR = 'B');
- any cylinder is initially filled with a volume of 255;
- square color is determined according to the RGB rule, that is, R = 0, G = 0, B = 0 is black, while R = 255, G = 255, B = 255 is white;
- any record in the utB table decreases the amount of paint in the B_VOL bottle and increases the amount of paint in a square by the same amount;
- B_VOL must be greater than 0 and less than or equal to 255;
- the amount of paint of the same color in one square cannot exceed 255;
- the amount of paint in the bottle cannot be less than 0;
- the drawing time (B_DATETIME) is specified with precision to the second, that is, it does not contain milliseconds.
WITH x AS (SELECT b_q_id, b_v_id, ball.v_color, b_vol as vol FROM utb JOIN utv ball ON utb.b_v_id = ball.v_id) , y AS ( SELECT b_q_id, v_color, totalcolor = CASE WHEN SUM(vol) > 255 THEN 255 ELSE SUM(vol) END FROM x GROUP BY b_q_id, v_color), z AS( SELECT b_v_id, totalbcolor = CASE WHEN SUM(b_vol) > 255 THEN 255 ELSE SUM(b_vol) END FROM utb GROUP BY b_v_id) , a AS (SELECT b_q_id, SUM(totalcolor) totalacolor FROM y GROUP BY b_q_id) , b AS (SELECT b_q_id,b_v_id, totalacolor FROM a, z) , c AS (SELECT DISTINCT b.b_q_id, totalacolor FROM b INNER JOIN utb ON (b.b_q_id = utb.b_q_id AND b.b_v_id = utb.b_v_id)) SELECT CAST(CAST(SUM(totalacolor) AS NUMERIC(8,2)) / (SELECT COUNT(*) FROM utq)AS NUMERIC(8,2)) FROM c
Comments My code is probably too long. But there is probably a shorter way to do it. But I still don't understand where I am going wrong. Please, help.
source to share
Here's another solution using
CASE WHEN ... THEN ... ELSE ... END
SELECT CONVERT(NUMERIC(15,2), ( SELECT SUM(total_paint) * 1.0 / COUNT(Q_ID) AS avg_paint FROM ( SELECT DISTINCT utQ.Q_ID, CASE WHEN SUM(B_VOL) IS NULL THEN 0 ELSE SUM(B_VOL) END AS total_paint FROM utQ LEFT JOIN utB ON utB.B_Q_ID = utQ.Q_ID GROUP BY utQ.Q_ID ) AS tmp ) )
source to share