pages/6_sql_stackoverflow.py

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine 
from PIL import Image

st.set_page_config(page_title="# Анализ данных StackOverflow")

st.markdown('# Анализ данных StackOverflow')

with st.expander("Описание проекта"):
    st.write("""
        Вы будете работать с базой данных StackOverflow — сервиса вопросов и ответов о программировании. 
    StackOverflow похож на социальную сеть — пользователи сервиса задают вопросы, отвечают на посты, оставляют комментарии и ставят оценки другим ответам.  
Вы будете работать с версией базы, где хранятся данные о постах за 2008 год, но в таблицах вы найдёте информацию и о более поздних оценках, которые эти посты получили.  

    Описание данных:

- Таблица badges:
    Хранит информацию о значках, которые присуждаются за разные достижения. Например, пользователь, правильно ответивший на большое количество вопросов про PostgreSQL, может получить значок postgresql. 
    - id	Идентификатор значка, первичный ключ таблицы
    - name	Название значка
    - user_id	Идентификатор пользователя, которому присвоили значок, внешний ключ, отсылающий к таблице users
    - creation_date	Дата присвоения значка
    
- Таблица post_types:
Содержит информацию о типе постов. Их может быть два:
    - Question — пост с вопросом;
    - Answer — пост с ответом.

    - id	Идентификатор поста, первичный ключ таблицы
    - type	Тип поста
- Таблица posts:
Содержит информацию о постах.

    - id	Идентификатор поста, первичный ключ таблицы
    - title	Заголовок поста
    - creation_date	Дата создания поста
    - favorites_count	Число, которое показывает, сколько раз пост добавили в «Закладки»
    - last_activity_date	Дата последнего действия в посте, например комментария
    - last_edit_date	Дата последнего изменения поста
    - user_id	Идентификатор пользователя, который создал пост, внешний ключ к таблице users
    - parent_id	Если пост написали в ответ на другую публикацию, в это поле попадёт идентификатор поста с вопросом
    - post_type_id	Идентификатор типа поста, внешний ключ к таблице post_types
    - score	Количество очков, которое набрал пост
    - views_count	Количество просмотров
- Таблица users:
Содержит информацию о пользователях.

    - id	Идентификатор пользователя, первичный ключ таблицы
    - creation_date	Дата регистрации пользователя
    - display_name	Имя пользователя
    - last_access_date	Дата последнего входа
    - location	Местоположение
    - reputation	Очки репутации, которые получают за хорошие вопросы и полезные ответы
    - views	Число просмотров профиля пользователя
- Таблица vote_types:
Содержит информацию о типах голосов. Голос — это метка, которую пользователи ставят посту. Типов бывает несколько: 
    - UpMod — такую отметку получают посты с вопросами или ответами, которые пользователи посчитали уместными и полезными.
    - DownMod — такую отметку получают посты, которые показались пользователям наименее полезными.
    - Close — такую метку ставят опытные пользователи сервиса, если заданный вопрос нужно доработать или он вообще не подходит для платформы.
    - Offensive — такую метку могут поставить, если пользователь ответил на вопрос в грубой и оскорбительной манере, например, указав на неопытность автора поста.
    - Spam — такую метку ставят в случае, если пост пользователя выглядит откровенной рекламой.

    - id	Идентификатор типа голоса, первичный ключ
    - name	Название метки
- Таблица votes:
    Содержит информацию о голосах за посты. 
    - id	Идентификатор голоса, первичный ключ
    - post_id	Идентификатор поста, внешний ключ к таблице posts
    - user_id	Идентификатор пользователя, который поставил посту голос, внешний ключ к таблице users
    - bounty_amount	Сумма вознаграждения, которое назначают, чтобы привлечь внимание к посту
    - vote_type_id	Идентификатор типа голоса, внешний ключ к таблице vote_types
    - creation_date	Дата назначения голоса
    """)



db_config = {
    'user': 'praktikum_student', # имя пользователя
    'pwd': 'Sdf4$2;d-d30pp', # пароль
    'host': 'rc1b-wcoijxj3yxfsf3fs.mdb.yandexcloud.net',
    'port': 6432, # порт подключения
    'db': 'data-analyst-advanced-sql' # название базы данных
    }  

connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(
        db_config['user'],
        db_config['pwd'],
        db_config['host'],
        db_config['port'],
        db_config['db'],
    )
engine = create_engine(connection_string)

def query_db(query):
    return pd.read_sql_query(query, con=engine)

query_1 = '''SELECT date_trunc('month', creation_date) as month_date, sum(views_count) as total_views
FROM stackoverflow.posts
WHERE extract( YEAR from creation_date) = '2008'
GROUP BY month_date
ORDER BY total_views DESC
'''
query_2 = '''SELECT u.display_name, count(DISTINCT p.user_id)
FROM stackoverflow.users as u
JOIN stackoverflow.posts as p ON p.user_id = u.id
JOIN stackoverflow.post_types as pt ON pt.id = p.post_type_id
WHERE pt.type = 'Answer' AND
p.creation_date::date BETWEEN u.creation_date AND (u.creation_date::date + INTERVAL '1 month')
GROUP BY u.display_name
HAVING count(p.user_id)>100
ORDER BY u.display_name
'''
query_3 = '''WITH  dt as (SELECT u.id
FROM stackoverflow.posts as p
JOIN  stackoverflow.users as u ON p.user_id = u.id
WHERE DATE_TRUNC('month', u.creation_date) = '2008-09-01' AND
DATE_TRUNC('month', p.creation_date) = '2008-12-01')
SELECT date_trunc('month', p.creation_date)::date as month, count(p.id)
FROM stackoverflow.posts as p
WHERE p.user_id in (SELECT * FROM dt) AND
EXTRACT(YEAR FROM p.creation_date) = '2008'
GROUP BY month
ORDER BY month DESC
'''
query_4 = '''SELECT user_id, AVG(avg_daily)
FROM (SELECT DISTINCT user_id, date_trunc('day', creation_date)::date as t,
count(id) OVER (PARTITION BY user_id, date_trunc('day', creation_date)::date) as avg_daily,
count(id) OVER (PARTITION BY user_id, date_trunc('month', creation_date)::date) as cnt
FROM stackoverflow.posts
WHERE date_trunc('month', creation_date)::date = '2008-08-01') as dt
WHERE cnt>120
GROUP BY user_id
ORDER BY AVG(avg_daily)
'''

examples = {'Выводит общую сумму просмотров постов за каждый месяц 2008 года':query_1,
            'Выводит имена самых активных пользователей, которые в первый месяц после регистрации (включая день регистрации) дали больше 100 ответов':query_2,
            'Выводит количество постов за 2008 год по месяцам. Отбирает посты от пользователей, которые зарегистрировались в сентябре 2008 года и сделали хотя бы один пост в декабре того же года.':query_3,
            'Найдет среднее количество постов пользователей в день за август 2008 года. Отберет данные о пользователях, которые опубликовали больше 120 постов за август. Дни без публикаций не учитывает.':query_4,
            }
with st.expander("Схема быза данных"):
    image = Image.open('image/Frame.png')
    st.image(image)
    
with st.expander("Примеры SQL запросов"):
    
    query = st.selectbox('Выберете один из запросов:', ('Выводит общую сумму просмотров постов за каждый месяц 2008 года',
                                                        'Выводит имена самых активных пользователей, которые в первый месяц после регистрации (включая день регистрации) дали больше 100 ответов',
                                                        'Выводит количество постов за 2008 год по месяцам. Отбирает посты от пользователей, которые зарегистрировались в сентябре 2008 года и сделали хотя бы один пост в декабре того же года.',
                                                        'Найдет среднее количество постов пользователей в день за август 2008 года. Отберет данные о пользователях, которые опубликовали больше 120 постов за август. Дни без публикаций не учитывает.',
                                                        ))
    
    st.code(examples[query], language="sql", line_numbers=False)
    if st.button("Запуск запроса"):
        st.markdown('## Результат запроса:')
        st.write(query_db(examples[query]))



def submit():
    st.session_state.title = st.session_state.widget
    st.session_state.widget = ""

st.text_input("Введите ваш SQL запрос", key="widget", on_change=submit)

if 'title' not in st.session_state:
    st.session_state.title = ""    

title = st.session_state.title    



# st.write(title)       
# title = st.text_input('Введите ваш SQL запрос')
        
if title:
    st.markdown('## Ваш запрос:')
    st.code(title, language="sql", line_numbers=False)
    st.markdown('## Результат запроса:')
    try:
        st.write(query_db(title))
    except:
        'Запрос неверен, убедитесь в правильности запроса'