Your analyses is useless. If your data is a mess. 8 steps to clean data in SQL 👇 𝗦𝘁𝗲𝗽 𝟬: 𝗨𝗻𝗱𝗲𝗿𝘀𝘁𝗮𝗻𝗱 𝗱𝗮𝘁𝗮 𝘀𝘁𝗿𝘂𝗰𝘁𝘂𝗿𝗲 ↳ Understand column names and types ↳ Check out a sample of the data 𝗦𝘁𝗲𝗽 𝟭: 𝗘𝘅𝗽𝗹𝗼𝗿𝗲 𝘁𝗵𝗲 𝗱𝗮𝘁𝗮 ↳ For numerical columns, check distributions ↳ For categorical variables, check frequencies 𝗦𝘁𝗲𝗽 𝟮: 𝗦𝘁𝗮𝗻𝗱𝗮𝗿𝗱𝗶𝘇𝗲 𝗱𝗮𝘁𝗮 𝗳𝗼𝗿𝗺𝗮𝘁𝘀 ↳ Convert strings to lower case or upper case ↳ Cast dates to readable format ↳ Remove white spaces 𝗦𝘁𝗲𝗽 𝟯: 𝗥𝗲𝗺𝗼𝘃𝗲 𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗲𝘀 ↳ Use DISTINCT to remove duplicate rows 𝗦𝘁𝗲𝗽 𝟰: 𝗙𝗶𝗹𝗹 𝗶𝗻 𝗻𝘂𝗹𝗹𝘀 𝗮𝗻𝗱 𝗺𝗶𝘀𝘀𝗶𝗻𝗴 𝘃𝗮𝗹𝘂𝗲𝘀 ↳ Fill with 0 (only when 0 is a meaningful default) ↳ Drop the data, if missing data makes the row unusable ↳ Fill with the average (for numerical data only) 𝗦𝘁𝗲𝗽 𝟱:  𝗦𝘁𝗮𝗻𝗱𝗮𝗿𝗱𝗶𝘇𝗲 𝗰𝗮𝘁𝗲𝗴𝗼𝗿𝗶𝗰𝗮𝗹 𝘃𝗮𝗿𝗶𝗮𝗯𝗹𝗲𝘀 ↳ Use CASE WHEN to clean the data 𝗦𝘁𝗲𝗽 𝟲: 𝗙𝗶𝗹𝘁𝗲𝗿 𝗼𝘂𝘁 𝗯𝗮𝗱 𝗱𝗮𝘁𝗮 ↳ Use WHERE to filter out unusable rows 𝗦𝘁𝗲𝗽 𝟳: 𝗥𝗲𝗻𝗮𝗺𝗲 𝗰𝗼𝗹𝘂𝗺𝗻𝘀 𝗳𝗼𝗿 𝗰𝗹𝗮𝗿𝗶𝘁𝘆 ↳ Use intuitive and clear column names 𝗦𝘁𝗲𝗽 𝟴: 𝗖𝗿𝗲𝗮𝘁𝗲 𝗿𝗲𝘂𝘀𝗮𝗯𝗹𝗲 𝘃𝗶𝗲𝘄𝘀 𝗼𝗿 𝘁𝗮𝗯𝗹𝗲𝘀 ↳ Create a view (doesn't take up more storage) ↳ Save as a table (faster for querying) ——— Ready to test your SQL skills? Check out these questions on Interview Master: 🙂 Easy SQL question: https://lnkd.in/gG_A8FWP 😉 Medium SQL question: https://lnkd.in/geQqR_uV 😄 Hard SQL question: https://lnkd.in/gRrNfcuK ——— ♻️ Found this useful? Repost it please! | 64 comments on LinkedIn