Skip to content

What is the error in below query ?

0

I am trying to implement below scenario : suppose there are 89 records in my source table and I want to pick only 15 records and these 15 records should be picked on the basis of proportion of values in column A,B and C. First look in A column , if equal weightage then go for column B and then column C.

WITH a_percentage AS ( SELECT CREDIT_TEAM_NAME, COUNT() AS count_a, (COUNT() * 100.0) / (SELECT COUNT(*) FROM u_s_rmiu.Sampling_MLC_Step6) AS percent_a FROM u_s_rmiu.Sampling_MLC_Step6 GROUP BY CREDIT_TEAM_NAME),

row_distribution AS ( SELECT CREDIT_TEAM_NAME, count_a, percent_a, ROUND((percent_a / 100) * 15, 0) AS allocated_rows FROM a_percentage ), ranked_data AS ( SELECT yt., DENSE_RANK() OVER (ORDER BY COUNT() OVER (PARTITION BY CREDIT_TEAM_NAME) DESC) AS rank_a, DENSE_RANK() OVER (PARTITION BY CREDIT_TEAM_NAME ORDER BY COUNT() OVER (PARTITION BY le_sector) DESC) AS rank_b, DENSE_RANK() OVER (PARTITION BY CREDIT_TEAM_NAME, le_sector ORDER BY COUNT() OVER (PARTITION BY Country_of_Incorporation_Cod) DESC) AS rank_c FROM u_s_rmiu.Sampling_MLC_Step6 yt )

SELECT * FROM ranked_data WHERE CREDIT_TEAM_NAME IN (SELECT CREDIT_TEAM_NAME FROM row_distribution) LIMIT 15;

asked 2 years ago220 views

2 Answers
0

The query you've provided appears to be attempting to implement a complex sampling strategy based on proportional representation and multiple ranking criteria. However, there are a few issues with the query that may be causing errors or unexpected results:

  1. The LIMIT clause at the end of the query is not sufficient to ensure you get the exact proportional representation you're aiming for. It will simply return the first 15 rows that match the WHERE condition, which may not align with your desired distribution across CREDIT_TEAM_NAME.

  2. The ranking logic (rank_a, rank_b, rank_c) is created, but not used in the final selection of rows. This means your intention to select based on column A, then B, then C is not being implemented.

  3. The row_distribution CTE calculates allocated_rows, but this information is not used in the final selection.

To fix these issues and implement your desired logic, you would need to:

  1. Use the allocated_rows from the row_distribution CTE to select the correct number of rows for each CREDIT_TEAM_NAME.

  2. Implement a row selection mechanism that uses the rank_a, rank_b, and rank_c columns to prioritize which rows to select within each CREDIT_TEAM_NAME group.

  3. Ensure that the total number of rows selected matches your target of 15.

A possible approach would be to use a ROW_NUMBER() function partitioned by CREDIT_TEAM_NAME and ordered by your ranking criteria, then select rows where this number is less than or equal to the allocated_rows for that CREDIT_TEAM_NAME. However, the exact implementation would depend on your specific requirements and the structure of your data.

Without more information about your specific database system and the exact error message you're receiving, it's difficult to provide a more precise diagnosis or solution. If you're getting a specific error message, that would be helpful information to include when troubleshooting SQL queries.
Sources
AWS query issue | AWS re:Post

answered 2 years ago

EXPERT

reviewed 2 years ago

0

Here are the key suggestions to improve the shared query along with the suggestions shared by Gen AI:

Record Selection: Replace DENSE_RANK() with ROW_NUMBER() for unique record selection
Ranking Columns: Combine the three separate ranking columns into a single ranking function
Duplicate Prevention: This prevents duplicate ranks and ensures proper hierarchical selection
Row Selection: Need to implement proper row selection based on allocated_rows
Partitioning: Add proper partitioning to maintain team-wise proportions
Ordering: Include proper ordering within each partition
AWS

answered 2 years ago

EXPERT

reviewed 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.