Assignment Day 2 - Introduction to SQL

Assignment Day 2 - Introduction to SQL

Please answer the questions below. Enter your SQL queries that provide the appropriate answers into the code chunks below the question.

The questions are designed to reflect the kinds of insights that you might be asked by a data team manager or executive. As a data scientist/analyst, it is your job to translate real life requests into code that can answer these questions. The most important part of this assignment, and all the ones that follow, is to develop a line of reasoning that you can implement with code in order to get the answer that you want to.

1. How many transactions are there in the transactions table?

select 
    count(*) as 'Number of transactions'
from 
    transactions;

2. How many customers are there in the customer database?

select 
    count(*) as 'Number of customers'
from 
    customers;

3. Are there the same number of customers in the transactions and customer databases? Why is it important that these figures are the same?

• we have seen from above answer that number of customers from customer table are 569887, now let us check number of distict customer is transaction table

select
    count(distinct customer)
from 
    transactions;

• from the result of above query we get 569887, this means that to make transaction must be registered in customer table,and means that at least each customer they have made one transaction.

4. How many years and months does this data cover?

5. What are all the different transaction types present in the transactions data?

select
     distinct transaction_id
from 
    transactions;

6. What is the most common transaction type?

select 
      transaction_id,count(*)
from 
      transactions
group by transaction_id 

order by count(*) desc

limit 1;

7. Which transaction type has the highest total amount (value) transacted? What is the average transaction size for each transaction type?

select 
      transaction_id, round(sum(amount),2) as 'total amount',avg(amount) 'Average'
from 
      transactions
group by transaction_id 

order by round(sum(amount),2) desc

limit 1;

8. Which transaction is done by the highest number of unique customers?

select 
      transaction_id,customer, count(*)
from 
      transactions 
group by 
      customer
order by 
      count(*) desc
limit 1;

9. What percentage of customers are female?

10. How many customers are 25 years old? How many customers are 60 years old? What does this say about financial inclusion?

select 
      count(*) 
from 
      customers
where age =24;


select 
      count(*) 
from 
      customers
where age =60;

11. What is the total value transacted by customers 18 years or younger? How many distinct customers are there 18 years or younger? Provide your answer in one table.