SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- creating table from export C tab 'transaction' which contains customer transaction details CREATE TABLE transaction_detail (`id` int, `datetime` varchar(10), `amount` decimal(6,2), `merchant_id` int, `credit_card_no` bigint) ; -- inserting data from export C tab 'transaction' into the transaction_detail table created in the above step INSERT INTO transaction_detail (`id`, `datetime`, `amount`, `merchant_id`, `credit_card_no`) VALUES (45, '2021-11-19', 44.99, 429, 4294294924941110), (46, '2021-11-19', 24.22, 55, 4294588829992440), (47, '2021-11-19', 44.98, 228, 4924585820595440), (48, '2021-11-19', 242.11, 244, 4924585819994210), (49, '2021-11-19', 69.55, 22, 4924585819994210), (50, '2021-11-19', 359.55, 888, 4924581055882000), (51, '2021-11-19', 1402.24, 98, 4924581220449220), (52, '2021-11-19', 445.23, 99, 4924582292499900), (53, '2021-11-19', 59.69, 88, 4942588295204420), (54, '2021-11-19', 552.59, 99, 4924581055882000), (55, '2021-11-19', 443.43, 48, 4924581055882000), (56, '2021-11-19', 23.43, 219, 4924581220449220), (57, '2021-11-19', 556.42, 124, 4924582292499900), (58, '2021-11-19', 256.25, 585, 4294588295204420), (59, '2021-11-19', 99.99, 229, 4924599204492810), (60, '2021-11-19', 2.35, 248, 4924258220049220) ; -- creating table from export C tab 'merchant' which contains merchant names paired with IDs CREATE TABLE Merchant_Lookup (`id` int, `merchant_name` varchar(29)) ; -- inserting data from export C tab 'merchant' into the Mercahant_Lookup table created in the above step INSERT INTO Merchant_Lookup (`id`, `merchant_name`) VALUES (22, 'Eden of the East S.A.'), (48, 'Pokemon Center Inc.'), (55, 'Log Pose Inc.'), (98, 'Loblaw'), (99, 'SDM Inc.'), (124, 'Evergarden Corp.'), (219, 'Scouts Legion'), (228, 'Leaf Village Inc.'), (229, 'Nook & Cranny'), (244, 'Life in a Bottle Co.'), (248, 'LEGO Inc.'), (248, 'Gardeners of the Galaxy Co.'), (429, 'Leaf Village Inc.'), (585, 'Uncharted Territories Limited'), (888, 'MGS Corp.') ; -- creating table from export B which contains customer profile CREATE TABLE customer_profile (`id` int, `name` varchar(18), `credit_card_no` bigint, `dob` varchar(10), `email_add` varchar(24), `active_flag` varchar(2)) ; -- inserting data from export B into the customer_profile table created in the above step INSERT INTO customer_profile (`id`, `name`, `credit_card_no`, `dob`, `email_add`, `active_flag`) VALUES (249428, 'Monkey D. Luffy', 4294294924941110, '1944-02-11', 'monkey@gmail.com', 'Y'), (249429, 'Naruto Uzumaki', 492458882999244, '1984-02-14', 'uzumaki.n@live.com', 'N'), (249430, 'Spike Spiegel', 4924585820595440, '1956-04-25', 'bebop492@gmail..com', 'Y'), (249431, 'Edward Elric', 4924585819994210, '9/22/1899', 'ed.not.tiny@gmail.com', 'Y'), (249432, 'Alphonse Elric', 4924585819994210, '1983-06-23', 'al.not.fma@live.com', 'Y'), (249433, 'Artoria Pendragon', 4924581055882000, '1989-12-12', 'fate.stay2494@gmail.com', 'Y'), (249434, 'Violet Evergarden', 4924581220449220, '1928-01-22', 'messenger_24@live.com', 'N'), (249435, 'Eren Jaeger', 4924582292499900, '2999-12-13', 'titans.ahoy@gmail.com', 'N'), (249436, 'Hououin Kyoma', 4942588295204420, '', 'into.the.gate@live.com', 'No'), (249437, 'Gene Starwind', 4924599204492810, '1993-02-18', 'outlaw29@gmail.com', 'Y'), (249438, 'Lelouch Lamperouge', 4924258220049220, '1974-05-31', 'vivalabritannia@live.com', 'Y') ; -- below query is performing basic profiling checks on each column in the transaction_detail table including, min value, max value, min length, avg length, max length, number and percentage of nulls, and number and percentage of distincy values select count(*) as Total_Record_Count, min(id) as ID_Min, max(id) as ID_Max, min(length(id)) as ID_Min_Length, sum(length(id))/count(*) as ID_Avg_Length, max(length(id)) as ID_Max_Length, sum(case when (id is null or id='') then 1 else 0 end) ID_Count_Nulls, (sum(case when (id is null or id='') then 1 else 0 end)/count(*))*100 ID_Percent_Nulls, count(distinct id) as ID_Distinct_Count, (count(distinct id)/count(*))*100 as ID_Distinct_Count_Percent, min(datetime) as Datetime_Min, max(datetime) as Datetime_Max, min(length(datetime)) as Datetime_Min_Length, sum(length(datetime))/count(*) as Date_Avg_Length, max(length(datetime))Datetime_Max_Length, sum(case when (datetime is null or datetime='') then 1 else 0 end) Datetime_Count_Nulls, (sum(case when (datetime is null or datetime='') then 1 else 0 end)/count(*))*100 as Datetime_Percent_Nulls, count(distinct datetime) as Datetime_Distinct_Count, (count(distinct datetime)/count(*))*100 as Datetime_Distinct_Count_Percent, min(amount) as Amount_Min, max(amount) as Amount_Max, min(length(amount)) as Amount_Min_Length, sum(length(amount))/count(*) as Amount_Avg_Length, max(length(amount)) as Amount_Max_Length, sum(case when (amount is null or amount='') then 1 else 0 end) Amount_Count_Nulls, (sum(case when (amount is null or amount='') then 1 else 0 end)/count(*))*100 as Amount_Percent_Nulls, count(distinct amount) as Amount_Distinct_Count, (count(distinct amount)/count(*))*100 as Amount_Distinct_Count_Percent, min(merchant_id) as Merchant_ID_Min, max(merchant_id) as Merchant_ID_Max, min(length(merchant_id)) as Merchant_ID_Min_Length, sum(length(merchant_id))/count(*) as Merchant_ID_Avg_Length, max(length(merchant_id)) as Merchant_ID_Max_Length, sum(case when (merchant_id is null or merchant_id='') then 1 else 0 end) Merchant_ID_Count_Nulls, (sum(case when (merchant_id is null or merchant_id='') then 1 else 0 end)/count(*))*100 as Merchant_ID_Percent_Nulls, count(distinct merchant_id) as Merchant_ID_Distinct_Count, (count(distinct merchant_id)/count(*))*100 as Merchant_ID_Distinct_Count_Percent, min(credit_card_no) as Credit_Card_No_Min, max(credit_card_no) as Credit_Card_No_Max, min(length(credit_card_no)) as Credit_Card_No_Min_Length, sum(length(credit_card_no))/count(*) as Credit_Card_No_Avg_Length, max(length(credit_card_no)) as Credit_Card_No_Max_Length, sum(case when (credit_card_no is null or credit_card_no='') then 1 else 0 end) Credit_Card_No_Count_Nulls, (sum(case when (credit_card_no is null or credit_card_no='') then 1 else 0 end)/count(*))*100 as Credit_Card_No_Percent_Nulls, count(distinct credit_card_no) as Credit_Card_No_Distinct_Count, (count(distinct credit_card_no)/count(*))*100 as Credit_Card_No_Distinct_Count_Percent from transaction_detail; -- below query is performing basic profiling checks on each column in the customer_profile table including, min value, max value, min length, avg length, max length, number and percentage of nulls, and number and percentage of distincy values select count(*) as Total_Record_Count, min(id) as ID_Min, max(id) as ID_Max, min(length(id)) as ID_Min_Length, sum(length(id))/count(*) as ID_Avg_Length, max(length(id)) as ID_Max_Length, sum(case when (id is null or id='') then 1 else 0 end) ID_Count_Nulls, (sum(case when (id is null or id='') then 1 else 0 end)/count(*))*100 ID_Percent_Nulls, count(distinct id) as ID_Distinct_Count, (count(distinct id)/count(*))*100 as ID_Distinct_Count_Percent, min(name) as Name_Min, max(name) as Name_Max, min(length(name)) as Name_Min_Length, sum(length(name))/count(*) as Name_Avg_Length, max(length(name)) as NAME_Max_Length, sum(case when (name is null or name='') then 1 else 0 end) Name_Count_Nulls, (sum(case when (name is null or name='') then 1 else 0 end)/count(*))*100 Name_Percent_Nulls, count(distinct name) as Name_Distinct_Count, (count(distinct name)/count(*))*100 as Name_Distinct_Count_Percent, min(credit_card_no) as Credit_Card_No_Min, max(credit_card_no) as Credit_Card_No_Max, min(length(credit_card_no)) as Credit_Card_No_Min_Length, sum(length(credit_card_no))/count(*) as Credit_Card_No_Avg_Length, max(length(credit_card_no)) as Credit_Card_No_Max_Length, sum(case when (credit_card_no is null or credit_card_no='') then 1 else 0 end) Credit_Card_No_Count_Nulls, (sum(case when (credit_card_no is null or credit_card_no='') then 1 else 0 end)/count(*))*100 as Credit_Card_No_Percent_Nulls, count(distinct credit_card_no) as Credit_Card_No_Distinct_Count, (count(distinct credit_card_no)/count(*))*100 as Credit_Card_No_Distinct_Count_Percent, min(dob) as DOB_Min, max(dob) as DOB_Max, min(length(dob)) as DOB_Min_Length, sum(length(dob))/count(*) as DOB_Avg_Length, max(length(dob)) as DOB_Max_Length, sum(case when (dob is null or dob='') then 1 else 0 end) DOB_Count_Nulls, (sum(case when (dob is null or dob='') then 1 else 0 end)/count(*))*100 DOB_Percent_Nulls, count(distinct dob) as DOB_Distinct_Count, (count(distinct dob)/count(*))*100 as DOB_Distinct_Count_Percent, min(email_add) as Email_Add_Min, max(email_add) as Email_Add_Max, min(length(email_add)) as Email_Add_Min_Length, sum(length(email_add))/count(*) as Email_Add_Avg_Length, max(length(email_add)) as EMAIL_Add_Max_Length, sum(case when (email_add is null or email_add='') then 1 else 0 end) Email_Add_Count_Nulls, (sum(case when (email_add is null or email_add='') then 1 else 0 end)/count(*))*100 Email_Add_Percent_Nulls, count(distinct email_add) as EMAIL_Add_Distinct_Count, (count(distinct email_add)/count(*))*100 as Email_Add_Distinct_Count_Percent, min(active_flag) as Active_Flag_Min, max(active_flag) as Active_Flag_Max, min(length(active_flag)) as Active_Flag_Min_Length, sum(length(active_flag))/count(*) as Active_Flag_Avg_Length, max(length(active_flag)) as Active_Flag_Max_Length, sum(case when (active_flag is null or active_flag='') then 1 else 0 end) Active_Flag_Count_Nulls, (sum(case when (active_flag is null or active_flag='') then 1 else 0 end)/count(*))*100 Active_Flag_Percent_Nulls, count(distinct active_flag) as Active_Flag_Distinct_Count, (count(distinct active_flag)/count(*))*100 as Active_Flag_Distinct_Count_Percent from customer_profile; -- below query is performing basic profiling checks on each column in the Merchant_Lookup table including, min value, max value, min length, avg length, max length, number and percentage of nulls, and number and percentage of distincy values select count(*) as Total_Record_Count, min(id) as ID_Min, max(id) as ID_Max, min(length(id)) as ID_Min_Length, sum(length(id))/count(*) as ID_Avg_Length, max(length(id)) as ID_Max_Length, sum(case when (id is null or id='') then 1 else 0 end) ID_Count_Nulls, (sum(case when (id is null or id='') then 1 else 0 end)/count(*))*100 ID_Percent_Nulls, count(distinct id) as ID_Distinct_Count, (count(distinct id)/count(*))*100 as ID_Distinct_Count_Percent, min(merchant_name) as Merchant_Name_Min, max(merchant_name) as Merchant_Name_Max, min(length(merchant_name)) as Merchant_Name_Min_Length, sum(length(merchant_name))/count(*) as Merchant_Name_Avg_Length, max(length(merchant_name)) as MERCHANT_NAME_Max_Length, sum(case when (merchant_name is null or merchant_name='') then 1 else 0 end) Merchant_Name_Count_Nulls, (sum(case when (merchant_name is null or merchant_name='') then 1 else 0 end)/count(*))*100 Merchant_Name_Percent_Nulls, count(distinct merchant_name) as Merchant_Name_Distinct_Count, (count(distinct merchant_name)/count(*))*100 as Merchant_Name_Distinct_Count_Percent from Merchant_Lookup; -- below query is pulling distinct column values with counts select distinct id, count(id) as ID_Count from transaction_detail group by id; -- below query is pulling distinct column values with counts select distinct datetime, count(datetime) as Datetime_Count from transaction_detail group by datetime; -- below query is pulling distinct column values with counts select distinct amount, count(amount) as Amount_Count from transaction_detail group by amount; -- below query is pulling distinct column values with counts select distinct merchant_id, count(merchant_id) as Merchant_ID_Count from transaction_detail group by merchant_id; -- below query is pulling distinct column values with counts select distinct credit_card_no, count(credit_card_no) as Credit_Card_No_Count from transaction_detail group by credit_card_no; -- below query is pulling distinct column values with counts select distinct id, count(id) as ID_Count from customer_profile group by id; -- below query is pulling distinct column values with counts select distinct name, count(name) as Name_Count from customer_profile group by name; -- below query is pulling distinct column values with counts and calculating age based on year of birth select distinct dob, count(dob) as DOB_Count, substr(curdate(),1,4) - substr(dob,1,4) as DOB_Age_Check from customer_profile group by dob; -- below query is pulling distinct column values with counts select distinct email_add, count(email_add) as Email_Add_Count, case when email_add like '%@%.%' then 'pass' else 'fail' as Email_Add_Format_Check from customer_profile group by email_add; -- below query is pulling distinct column values with counts select distinct active_flag, count(active_flag) from customer_profile group by active_flag; -- below query is pulling column values with counts, lengths and additional information from the table to understand duplicates and length issues select * from -- below subquery is getting the distinct values and length, count for the column then joining to the same table to suplement counts with customer information (select distinct credit_card_no, count(credit_card_no), length(credit_card_no) from customer_profile group by credit_card_no) x join customer_profile on customer_profile.credit_card_no = x.credit_card_no; -- below query is pulling column values with counts and additional information from the table to understand duplicates select * from Merchant_Lookup join -- below subquery is getting the distinct values and count for the column then joining to the same table to suplement counts with customer information (select distinct id, count(id) from Merchant_Lookup group by id) x on x.id = Merchant_Lookup.id join -- below subquery is getting the distinct values and count for the column then joining to the same table to suplement counts with customer information (select distinct merchant_name, count(merchant_name) from Merchant_Lookup group by merchant_name) b on b.merchant_name = Merchant_Lookup.merchant_name;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear