Explicación de la estructura de la base de datos de ejemplo

Hola les comparto los apuntes que tome sobre este curso, en algunas clases ahonde un poco mas, espero les sean de utilidad, no olviden dejar el like en mi tutorial de este curso. gracias totales

https://github.com/rb-one/Curso_de_PostgreSQL_Aplicado_a_Ciencia_de_Datos/blob/master/Notes/notes.md

Querys:

-Top 10 de películas con mayor facturación.
-Categorías con mayor facturación.
-Empleado que mas venta realiza en $.
-Ticket promedio por cliente en $.
-Contribución por categoría (Margen).

si tu quieres generar el diagrama ERD desde PostgreSQL para poder identificar mejor cada una de las relaciones. PostgreSQL te da la opción solo dando click derecho sobre tu base de datos.

Las 10 películas más rentadas

SELECT p.titulo, COUNT(p.pelicula_id) as c FROM peliculas as P
JOIN inventarios as i ON i.pelicula_id = p.pelicula_id
JOIN rentas as r on r.inventario_id = i.inventario_id
GROUP BY p.titulo, i.pelicula_id
ORDER BY c DESC LIMIT 10

Considero que la siguiente información a ser extraida podría ser util:

1. Pelicula mas rentada.
2. Actor con mas peliculas.
3. Usuario con mayor rentas.
4. Usuario con mayor pagos.
5. Tiendas con mayor trabajadores.
6. Ciudades con menos usuarios.

Los actores con las categorías de películas en que más participan.

SELECT a.nombre, a.apellido, c.nombre, COUNT(p.pelicula_id)
FROM actores a
JOIN peliculas_actores pa ON a.actor_id=pa.actor_id
JOIN peliculas p ON pa.pelicula_id=p.pelicula_id
JOIN peliculas_categorias pc ON p.pelicula_id=pc.pelicula_id
JOIN categorias c ON pc.categoria_id=c.categoria_id
GROUP BY a.nombre, a.apellido, c.nombre
ORDER BY COUNT(p.pelicula_id) DESC;

Fue harta prueba y error pero di con el resultado.

si una pelicular tiene varios idiomas y un idioma puede estar en varias películas, por que no se crea también la tabla relacional de ellas?

Se me ocurre listar las tiendas TOP en base a cuánto dinero recaudó por el alquiler.
También se me ocurre listar las películas TOP alquiladas, sin importar la tienda, luego se podría buscar las tiendas TOP en donde se alquiló poco las películas TOP, es decir, sería interesante ver por qué en algunas tiendas no se alquiló mucho las películas top.

Reto

Me interesaria saber que peliculas, existen en cada tienda, haría un select a inventarios y haría un inner join donde pelicula_id de inventarios es igual a pelicula_id de peliculas

Este es mi Query para calcular el Top 10 de peliculas con mas alta facturacion.

SELECT pelicula_id, titulo, SUM(cantidad)
FROM (
	SELECT pe.pelicula_id, pe.titulo, pa.cantidad FROM public.peliculas AS pe
INNER JOIN public.inventarios AS i  ON pe.pelicula_id = i.pelicula_id
INNER JOIN public.rentas AS r ON i.inventario_id = r.inventario_id
INNER JOIN public.pagos AS pa ON r.renta_id = pa.renta_id
) AS Tabla_facturacion
GROUP BY pelicula_id, titulo
ORDER BY SUM(cantidad) desc
LIMIT 10;

Por si eres principiante, como yo, aquí van algunas consultas de nivel básico (MUY BÁSICO) para calentar:

Película con mayor precio de renta

Top 5 de peliculas con mayor precio de renta

Top 3 de películas con mayor duración donde incluya la clasificación

¿Qué empleado ha registrado el mayor numero de pagos?

SELECT COUNT(pagos.pago_id) as pagos_x_empleado, CONCAT(empleados.nombre, ' ', empleados.apellido) as trabajador
FROM pagos
INNER JOIN empleados ON pagos.empleado_id = empleados.empleado_id
GROUP BY empleados.empleado_id
ORDER BY pagos_x_empleado DESC;

Excelente, el manejo de cursores

![](

Un reporte de ingresos por tienda en los últimos 6 meses

Sinceramente el Diagrama ERD generado por pgadmin es un asco

Me gustaría saber :
-Zona con más ventas.
-Top ten de tiendas con mayores ventas.
-Las tres tiendas con menores ventas.

• Vendedores destacados por zona.
  -Edad promedio de los clientes.
  -Los 3 géneros más rentables.
  -Actores más populares.

Les dejo una aplicacion para conectarse a la base de datos y extraer y visualizar el modelo de la misma.

https://dbschema.com/

-top 5 clientes mas frecuentes

• top tiendas con mas ventas
  -promedio de edad de clientes
  -películas mas rentadas

Se me ocurre que se le puede hacer todo tipo de select. Para filtrar diferentes datos segun lo que se nos pida.

**Ejersicio de esta clase: **

-Categoria de peliculas mas vendidas
-Peliculas que no se venden x cantidad de tiempo, para oofrcer promos
-Clientes que mas compran para establecer categorias y ofrecer promos
-Fechas con mayores ventas
-Tiendas que mas ventas generan

Querys
Peliculas mas rentadas
Clientes que mas rentan peliculas
Empleados que mas rentan peliculas
Clasificacion mas rentada
Ingresos
Ciudad donde mas se rentan peliculas
Duracion promedio de peliculas mas rentadas

• Cuantos actores han trabajado en películas
• la tienda donde rentan mas películas y menos.

súper cool todo esto :3

Ventas realizadas por cada empleado
Tengo mis dudas, me arroja que la facturación más alta es de 31059 y lo hago de otra forma y me arroja poco más de 30000. Este es el código

-- Empleados con mayor facturacion
SELECT CONCAT(nombre,' ', apellido) AS full_name, SUM(cantidad) AS sales
FROM pagos
INNER JOIN empleados USING (empleado_id)
GROUP BY full_name
ORDER BY sales DESC;

Cantidad de películas por categorías

SELECT c.nombre, 
	COUNT(pc.categoria_id) AS cat
FROM categorias AS c
JOIN peliculas_categorias AS pc ON pc.categoria_id = c.categoria_id
GROUP BY c.nombre, pc.categoria_id
ORDER BY cat DESC
LIMIT 10
;

Para una estrategia de penetracion de mercado:
-Top paises con mayor cantidad de compra(sea 5 paises o 10)
-Tipo de peliculas que consumen en cada paises
-Actores asociados

Estrategia de alto nivel de mercado:
-Top recaudacion de peliculas
-Top peliculas consumidas
-Costo de peliculas con mas recaudacion
-Top peliculas mas vistas
(en caso de querer generar una pelicula redituable)
-Actores asociados a peliculas mas vistas y consumidas
-Tematica de pelicula

• Ventas por ciudad
• Ventas por tienda
• Ventas por año
• Categorías más vendidas
• Directores más eficientes según ventas
• Ventas por idioma película

Queries:
-Top10 películas más rentadas
-Bottom 5 de tiendas en revenue
-Rentas por sexo/género de los clientes
-Costo promedio del ticket final
-Top 5 actores y Top 5 actrices que más se rentan
-Inventario en $$$ vs Bottom 5 vs Top5 de tiendas en revenue

1. Qué ciudades rentan más películas
2. Qué (categoría o película) trae mayor dinero a la tienda
3. Qué empleado ganó más dinero
4. Duración media de cada película

esta muy buena la bd para mejorar la skills !!

Algunas sugerencias:

• Las películas con mayor y menor precio de renta
• Los actores con mayor número de películas y un patrón de participación de acuerdo a la clasificación
• Un mapa de distribución para ver la relación entre tiendas y empleados, con la posibilidad de mejorar el transporte (si es una tienda física)

Se me ocurre hacer este query para saber que tipo de película es rentada por ciudad, podríamos ser más específicos y hacer una consulta por sector demográfico. Esto para que nos de una idea que tipo de película rentan nuestros clientes según el sector demográfico.

Podríamos cruzar está información con la de la población y preparar nuestro inventario a través de un análisis de la demanda basada en estos datos, el tipo de clasificación que buscan nuestro clientes según su ciudad o sector demográfico (podríamos buscar la edad de quienes rentan las películas y agruparla en rangos).

SELECT	ciudad,
	COUNT(clasificacion) AS peliculas_rentadas,
	clasificacion
FROM peliculas AS p
INNER JOIN inventarios AS i ON p.pelicula_id = i.pelicula_id
INNER JOIN rentas AS r ON i.inventario_id = r.inventario_id
INNER JOIN clientes AS c ON r.cliente_id = c.cliente_id
INNER JOIN direcciones AS d ON c.direccion_id = d.direccion_id
INNER JOIN ciudades AS ci ON d.ciudad_id = ci.ciudad_id
GROUP BY ciudad, clasificacion
ORDER BY peliculas_rentadas DESC;

Se podría obtener, el mayor número de peliculas vendidas por género por ciudad para obtener una mejor perspectiva de los gustos regionales, tambien se podría obtener las rentas con las ventas por categoria por ciudad y por tienda. Se puede obtener el precio con el tipo de cambio y juntarlo con los pagos totales para saber la rentabilidad total. Se podría obtener las peliculas más vendidas por lenguaje tambien para conocer los gustos de la región en lenguaje de visualización.

A seguir aprendiendooooo

Hice de esta forma las 10 mas rentadas:

SELECT peliculas.pelicula_id, peliculas.titulo, SUM(pagos.cantidad) AS Mas_Rentada
FROM peliculas
INNER JOIN inventarios ON peliculas.pelicula_id = inventarios.pelicula_id
INNER JOIN rentas ON inventarios.inventario_id = rentas.inventario_id
INNER JOIN pagos ON rentas.renta_id = pagos.renta_id
GROUP BY peliculas.pelicula_id
ORDER BY Mas_Rentada DESC
LIMIT 10

Si hay algo mal les pido si me avisan.
Gracias.

interesante

¿Cómo puedo obtener si una película tiene varios idiomas con las relaciones que existen?

• Películas mas rentadas
• Cantidad promedio de películas rentadas por cliente
• Cantidad de rentas por Empleado
• Películas no retornadas a la tienda
• Top ten películas por categoría

Se pueden geolocalizar las tiendas de manera que se sepa por sector qué tipo de películas se rentan más. Cruzando esa info, por ejemplo, con variables sociodemográficas del tipo de clientes.

Una consulta podría ser conocer cual es la película mas rentada en el último año por ciudad.

Muy bien explicado.
Solo una acotación, podrian tener otro script de carga de los datos desde insert, ya que si se tiene una base de datos desde docker, se pone complejo cargar un dump desde docker.

• Peliculas más rentadas con sus ingresos
• Categorías con más peliculas
• Actores con más participaciones

Top 10 de clientes con mas rentas de películas realizadas:

select count(*) cantidad_compras, CONCAT(nombre, ’ ', apellido) as "Nombre del Cliente"
from rentas
inner join clientes using(cliente_id)
group by cliente_id
order by cantidad_compras desc
limit 10

Variación del tipo de cambio en un trimestre económico.

Qué tipo de peliculas han pagado más los clientes a lo largo de los años
Qué empleado ha generado más rentas
Precio de peliculas más solicitadas
Direccion de los clientes que piden mas peliculas (Para identificar esa Region)