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; select distinct id, count(id) from transaction_detail group by id; select distinct datetime, count(datetime) from transaction_detail group by datetime; select distinct amount, count(amount) from transaction_detail group by amount; select distinct merchant_id, count(merchant_id) from transaction_detail group by merchant_id; select distinct credit_card_no, count(credit_card_no) from transaction_detail group by credit_card_no; select distinct id, count(id) from customer_profile group by id; select distinct name, count(name) from customer_profile group by name; select distinct dob, count(dob), substr(curdate(),1,4) - substr(dob,1,4), curdate() - dob from customer_profile group by dob; select distinct email_add, count(email_add) from customer_profile group by email_add; select distinct active_flag, count(active_flag) from customer_profile group by active_flag; select * from (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; select * from Merchant_Lookup join (select distinct id, count(id) from Merchant_Lookup group by id) x on x.id = Merchant_Lookup.id join (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