Centre for Data Science
Institute of Technical Education & Research, SOA, Deemed to be University
Introduction to Data Analysis with SQL (CSE 3195)
A SSIGNMENT-2
1. Write a query to retrieve the total sales for each year in the ”Retail and food services” category?
2. Compare and visualize the sales of ”Book stores”, ”Sporting goods stores”, and ”Hobby, toy, and
game stores” for each year?
3. Discuss how can you calculate the difference in sales between ”Men’s clothing stores” and ”Women’s
clothing stores” for each year, and how to order the results by year.
4. Write a query to calculate the percentage of sales contributed by ”Men’s clothing stores” and ”Women’s
clothing stores” to the total sales of both stores for each month. How does using a window function
simplify this calculation?
5. What PostgreSQL query is used to calculate a rolling 12-month average of sales for ”Women’s cloth-
ing stores” and how would you ensure that only data from January 1993 onwards is included?
6. How can you calculate the cumulative sales, year-to-date, for ”Women’s clothing stores” using a
window function?
7. Discuss the PostgreSQL query to compare the sales for the same month across different years for
”Book stores” and identify any growth or decline.
8. What PostgreSQL query would you use to identify the first term start date for each legislator in the
legislators terms table?
9. How can you determine the retention of legislators by calculating the difference in years between
their current term and their first term using PostgreSQL?
10. Describe how you would compute the size of a legislator cohort and the percentage retained over
multiple periods.
11. What query will you use to display retention percentages for each period and group them by year
using PostgreSQL?
12. How can you create a query to measure the survivorship of legislators, focusing on those who have
served for at least 10 years, and calculate the percentage that survived?
13. How would you calculate the number of legislators who have completed at least 5 terms and compute
the percentage of the cohort that survived these terms?
14. What PostgreSQL query can be used to find the number of representatives who started in each century
and analyze the retention rate for those who also served as senators?
15. Explain the query to group legislators based on their first type of term (e.g., representative or senator)
and calculate the number of terms they served within the first 10 years of their career.
1
1
For Question No. 1 to 7, use the US Retail sales dataset. For subsequent questions, use the Legislators terms dataset.
([Link]
1