trainingbypackt / sql-for-data-analytics Goto Github PK

Perform fast and efficient data analysis with the power of SQL

License: MIT License

Jupyter Notebook 87.20% PLpgSQL 12.80%

sql window-function index-scan sequential-scan hash-scan join killing-queries datetime geospatial copy

sql-for-data-analytics's Introduction

SQL for Data Analytics

Understanding and finding patterns in data has become one of the most important ways to improve business decisions. If you know the basics of SQL, but don’t know how to use it to gain business insights from data, this course is for you.

SQL for Data Analysis covers everything you need progress from simply knowing basic SQL to telling stories and identifying trends in data. You’ll be able to start exploring your data by identifying patterns and unlocking deeper insights. You’ll also gain experience working with different types of data in SQL, including time-series, geospatial, and text data. Finally, you’ll understand how to become productive with SQL with the help of profiling and automation to gain insights faster.

By the end of the course, you’ll able to use SQL in everyday business scenarios efficiently and look at data with the critical eye of analytics professional.

What you will learn

Perform advanced statistical calculations using the WINDOW function
Use SQL queries and subqueries to prepare data for analysis
Import and export data using a text file and psql
Apply special SQL clauses and functions to generate descriptive statistics
Analyze special data types in SQL, including geospatial and time data
Optimize queries to improve their performance for faster results
Debug queries that won’t run
Use SQL to summarize and identify patterns in data

The examples of this title has been implemented in the Windows/MAC/Linux operating system.

Software Requirement

You’ll also need the following software installed in advance:

Loading the sample datasets

You can find updated instructions on loading the sample datasets via this link.

sql-for-data-analytics's People

Contributors

Stargazers

Watchers

Forkers

rachanakaria vibhutig13 kukku netgary0430 tedhopton robintux shrikeshpandey saibby dpelletier282 renji1943 ggarrido28 dadhikari96 sandeepsonare eugeniousone barrettnb bbhatti3 hughsaar8 badouatom killu7 jldaniel77 andresl8 anotherjonlee shruthibaskar1 byudysky pnaka1407 digi-metal cisc3415 rajkumarbhc ayush-06 david0987 cmcduffie luisedo bhav11 jingweimo vinodsunkara vamsipanchagnula nallurip avinash468 nikhilpinapati suma-ai deepavthomas sharukhmd antonkulik1980 bshah89 chibak2 hiralpurohit9 mperezagua sumerasalim rishi-dave1107 pepapalacios nbermingham kuldeepsingh0694 girijad92 ockapp rutvikmounty uribejs zmantx manasa245 tmuppalla neerati daominhsang longshot2k20 sheethal546 limbachi jbyrne89 adityagoudbindi naga-nandini saikomeri kshirsagaryuvraj aidanvu1992 levterov101 relekarr daditirumal israhadii kirandeepkaler jvl98l oxw-118 f1refly aasthasin anusha18 rbuddi271 aswinkumar92 pboroma louiethirteen saisrinivasduduku upcsjt a6k7rii emmanuelcodev regina-active-pku korykorir ediouslo aletheuin phani1304 richard90king chintu2427 sarah-emslie gfreecss taylmurp dexterboxer giacomobrogi

sql-for-data-analytics's Issues

error in exercise 19 from lesson 6!

Hi there,
I'm getting an error in the line I'm writing. I get: ERROR: relation "customers" does not exist
LINE 4: FROM customers

BUT customers do exist.

Error: Chapter 9 Activity 18

When doing a select * from sales_transaction_date, there is no "count" listed so the Step.2 fails to execute.
in fact, bat_sales_daily does not exist either, but sales does exist.

SELECT *, sum(count) OVER (ORDER BY sales_transaction_date) INTO bat_sales_growth FROM sales;
ERROR: column "count" does not exist
LINE 1: SELECT *, sum(count) OVER (ORDER BY sales_transaction_date) ...
^
SQL state: 42703
Character: 15

Are there steps before this?

Data Analytics

Sql

how this book is different from same authors the applied analytics work shop ?

data.dump

Hi,

I continue to get the error:
psql: error : data.dump: No such file or directory

I have restarted the process and checked everything even moved the location of the file thinking that may help but I keep getting the same error when I try to load the data.dump file.

Any help will be much appreciated.

Download file

i cant download the data.dump, everytime i clicked the "download" button for it, it instead opened the file on the browser,I also tried but also couldn't,is there a way I can download it? thank you so much.

SQL

Loading the sample data sets -- Linux

I have created the data base sqlda but when I try to load it with data using "psql -d sqlda data.dump" it gives me the following error:
psql: error: FATAL: Peer authentication failed for user "data.dump"

How to download csv files

Hi,

Are the csv files still available. I am not able to download any of them. I do not see an option to. If I right click on the file name I can only save the link. Any help would be appreciated! Thank you!

Loading the Sample Databases

In the book, it states "To install the database on PostgreSQL, copy the data.dump file from the Datasets folder in the GitHub repository of the book.......Then, load the data.dump file from a command line using the command:
psql < data.dump"

Can someone please provide clarification on how this is done?? I've had the same issue as someone else when clicking on file that it brings up the code in a separate window. The book did not do a good job at explaining much of anything in terms of downloading software or loading the data.

Issue with the provided solution: Lesson04, Exercise 15

The question requests the customer count for states that have at least 1,000 customers who have purchased from ZoomZoom.

The solution provided ([https://github.com/TrainingByPackt/SQL-for-Data-Analytics/blob/master/Lesson04/Exercise15/Exercise15.sql] :

SELECT state, COUNT(*) FROM customers GROUP BY state HAVING COUNT(*)>=1000 ORDER BY state;

This returns the count of customer records, by state, having a customer record count >=1,000. This does not consider whether a customer has purchased or not.

Should the solution not be:

select c.state, count(distinct c.customer_id) from customers c inner join sales s on c.customer_id =s.customer_id group by c.state having count(distinct c.customer_id)>=1000 order by c.state;

To ensure we are only counting, at State level, distinct customers that have purchased a product.

Lesson 8 Activity 15

This code didn't work when I ran it.

Is it because it is written as?:
sqlda$# END; $channel_avg$
and should be?:
sqlda$# END; $sales_avg$

Importing data.dump

I'm having issues trying to import data.dump into pgadmin4. I've also tried loading it to MySQL workbench with no success. Any chance that this file comes in .csv or in a different file format?