day9.sqbpro

<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="/Users/maximilianolopezsalgado/data_projects/sql-playground/databases/day9.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="1500"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><current_table name="4,9:maincran_logs"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="cran_logs" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_"><sort/><column_widths><column index="1" value="129"/><column index="2" value="81"/><column index="3" value="91"/><column index="4" value="81"/><column index="5" value="69"/><column index="6" value="114"/><column index="7" value="196"/><column index="8" value="132"/><column index="9" value="67"/><column index="10" value="45"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">CREATE TABLE cran_logs (
    download_date TEXT,
    time TEXT,
    size INTEGER,
    r_version TEXT,
    r_arch TEXT,
    r_os TEXT,
    package TEXT,
    version TEXT,
    country TEXT,
    ip_id INTEGER
);

-- 9.1 Give the package name and how many times they're downloaded. Order by the 2nd column descending.
SELECT package AS Package_name, Count(*) AS Download_count 
FROM cran_logs
GROUP BY package
ORDER BY 2 DESC 
;

-- 9.2 Give the package ranking (based on how many times it was downloaded) during 9AM to 11AM
SELECT package, Count(*) AS download_Count
FROM cran_logs
WHERE strftime('%H:%M:%S', time) BETWEEN '09:00:00' AND '11:00:00'
GROUP BY package
ORDER BY Download_count DESC;

-- 9.3 How many records (downloads) are from China (&quot;CN&quot;) or Japan(&quot;JP&quot;) or Singapore (&quot;SG&quot;)?
SELECT country, COUNT(*) AS Download_count
FROM cran_logs
WHERE country IN ('CN', 'JP', 'SG')
GROUP BY country;

-- 9.4 Print the countries whose downloads are more than the downloads from China (&quot;CN&quot;)
SELECT country, COUNT(*) AS Country_count 
FROM cran_logs
GROUP BY country
HAVING COUNT(*) &gt; (
	SELECT COUNT(*)
	FROM cran_logs
	WHERE country = 'CN'
);

-- 9.5 Print the average length of the package name of all the UNIQUE packages
SELECT AVG(LENGTH(package)) AS Avg_lenght
FROM (
	SELECT DISTINCT package
	FROM cran_logs
) AS Unique_packages;

-- 9.6 Get the package whose download count ranks 2nd (print package name and its download count).
SELECT package, COUNT(*) AS Download_count
FROM cran_logs
GROUP BY package
ORDER BY Download_count DESC
LIMIT 1 OFFSET 1;

-- 9.7 Print the name of the package whose download count is bigger than 1000.
SELECT package
FROM cran_logs
GROUP BY package 
HAVING COUNT(*) &gt; 1000;

-- 9.8 The field &quot;r_os&quot; is the operating system of the users.
    -- 	Here we would like to know what main system we have (ignore version number), the relevant counts, and the proportion (in percentage).
SELECT r_os AS OS_Name, COUNT(*) AS OS_Count
FROM cran_logs
ORDER BY OS_Count DESC 
LIMIT 1;</sql><current_tab id="0"/></tab_sql></sqlb_project>